Home » SQL & PL/SQL » SQL & PL/SQL » partitioned table working as expected
| partitioned table working as expected [message #22181] |
Fri, 27 September 2002 05:55  |
Tracy
Messages: 43
Registered: January 2000
|
Member |
|
|
I had a query that was taking a long time to execute because of the large number of rows. After hash paritioning the table, the query execution time still did not improve.
However, when I specified the partition in the query, the time improved greatly. However, the application that will run this query will never know which partition in order to specify.
Any suggestions please on what I can do? I thought the query was supposed to automaticaly figure out which partition to scan so I don't have to specify.
thanks,
Tracy
|
|
|
|
|
|
| Re: partitioned table working as expected [message #22188 is a reply to message #22181] |
Fri, 27 September 2002 08:09  |
Tracy
Messages: 43
Registered: January 2000
|
Member |
|
|
TABLE DEFINITION:
CREATE TABLE FCBR_DOC_PART (
DOCKEY NUMBER NOT NULL,
DOC_NAME VARCHAR2 (128) NOT NULL,
DOC_ID VARCHAR2 (128),
HEADLINE VARCHAR2 (210),
BROKER_CODE VARCHAR2 (10) NOT NULL,
SITE VARCHAR2 (10),
STAMP DATE,
PUB_DATE DATE,
DOC_DATE DATE NOT NULL,
FCTIME DATE,
REL_DATE DATE,
PROD_CODE VARCHAR2 (10),
GROUP_CODE VARCHAR2 (10),
NUM_PAGE NUMBER (4),
BSIZE NUMBER,
DN_INSTLIST NUMBER NOT NULL,
DN_ENTLIST VARCHAR2 (128),
DN_ANALIST VARCHAR2 (128),
DN_INDUSTLIST VARCHAR2 (128),
DN_REGLIST VARCHAR2 (128),
USERKEY NUMBER,
DOC_TYPE VARCHAR2 (10),
URL VARCHAR2 (2038),
PRIMARY KEY ( DOCKEY ))
NOCACHE
PARTITION BY HASH (DOCKEY)
PARTITIONS 12
STORE IN ( FCBR_DOC_PART_1,FCBR_DOC_PART_2,FCBR_DOC_PART_3,
FCBR_DOC_PART_4,FCBR_DOC_PART_5,FCBR_DOC_PART_6,FCBR_DOC_PART_7,
FCBR_DOC_PART_8,FCBR_DOC_PART_9,FCBR_DOC_PART_10,FCBR_DOC_PART_11,
FCBR_DOC_PART_12);
CREATE INDEX FCBR_DOC_PART_BRKR_CODE ON
FCBR_DOC_PART(BROKER_CODE)
LOCAL STORE IN ( MNYBX_INDEX);
CREATE INDEX FCBR_DOC_PART_DCKY_BRKRCD_NPG ON
FCBR_DOC_PART(DOCKEY, BROKER_CODE, NUM_PAGE)
LOCAL STORE IN ( MNYBX_INDEX);
CREATE INDEX FCBR_DOC_PART_DOCKEY_BRKRCODE ON
FCBR_DOC_PART(DOCKEY, BROKER_CODE)
LOCAL STORE IN ( MNYBX_INDEX);
CREATE UNIQUE INDEX FCBR_DOC_PART_DOC_NAME ON
FCBR_DOC_PART(DOC_NAME)
TABLESPACE MNYBX_INDEX PCTFREE 10 STORAGE(INITIAL 1048576 NEXT 1048576 PCTINCREASE 0 )
;
CREATE INDEX FCBR_DOC_PART_NUM_PAGE ON
FCBR_DOC_PART(NUM_PAGE)
LOCAL STORE IN ( MNYBX_INDEX);
CREATE INDEX FCBR_DOC_PART_PUB_DATE ON
FCBR_DOC_PART(PUB_DATE)
LOCAL STORE IN ( MNYBX_INDEX);
CREATE INDEX FCBR_DOC_PART_REL_DATE ON
FCBR_DOC_PART(REL_DATE)
LOCAL STORE IN ( MNYBX_INDEX);
CREATE INDEX FCBR_DOC_PART_STAMP ON
FCBR_DOC_PART(STAMP)
LOCAL STORE IN ( MNYBX_INDEX_LARGE);
QUERY:
SELECT /*+ FIRST_ROWS */ d.dockey, d.doc_name, d.doc_id, d.headline,
TO_CHAR(d.rel_date, 'Mon DD HH:MI A.M.'), d.bsize, d.doc_type,
d.group_code, d.dn_instlist, d.rel_date, refb.name, d.prod_code,
TO_CHAR(d.rel_date, 'YYYY/MM/DD/HH24/MI/SS'), fdt.TICKER, fdt.INDUS_CODE,
DECODE(fdt.TICKER, NULL, NULL,
(SELECT fi.industry FROM dmgmt.fcbr_industry fi
WHERE fdt.indus_code=fi.industry_code)) industry
FROM dmgmt.FCBR_DOC_PART d,dmgmt.fcbr_broker refb,
(SELECT ticker, dockey, indus_code FROM dmgmt.FCBR_DOC_TICKER WHERE primary_flag='Y') fdt
WHERE d.broker_code=refb.broker_code AND
EXISTS (SELECT 1 FROM dmgmt.FCBR_DOC_TICKER dt
WHERE dt.dockey=d.dockey AND (dt.ticker IN ('IBM'))
)
AND EXISTS (SELECT 1 FROM dmgmt.FCBR_DOC_COUNTRY dc WHERE dc.dockey=d.dockey
AND dc.country_code IN ('CUS'))
AND EXISTS (SELECT 1 FROM dmgmt.fcbr_prodent entp
WHERE entp.prod_code=d.prod_code AND entp.prodentkey=143)
AND EXISTS ( SELECT 1 FROM dmgmt.FCBR_DOC_ENTITLEMENT de
WHERE de.dockey=d.dockey AND EXISTS (SELECT 1 FROM dmgmt.fcbr_mlent entm
WHERE entm.ml_code=de.ml_code AND entm.mlentkey=163))
AND d.rel_date >= ( SYSDATE - 60) AND d.rel_date < SYSDATE AND d.dockey = fdt.dockey(+) ORDER BY d.rel_date DESC
EXPLAIN PLAN (from Embarcadaro Rapid SQL):
STATEMENT_ID TIMESTAMP REMARKS OPERATION OPTIONS OBJECT_NODE OBJECT_OWNER OBJECT_NAME OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER SEARCH_COLUMNS ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID
51253668 9/27/2002 9:35:50 AM SELECT STATEMENT HINT: FIRST_ROWS 0 4764 4764 2 298
51253668 9/27/2002 9:35:50 AM SORT ORDER BY 1 0 1 4764 2 298
51253668 9/27/2002 9:35:50 AM FILTER 2 1 1
51253668 9/27/2002 9:35:50 AM NESTED LOOPS OUTER 3 2 1 4762 2 298
51253668 9/27/2002 9:35:50 AM NESTED LOOPS 4 3 1 4759 1 131
51253668 9/27/2002 9:35:50 AM PARTITION HASH ALL 5 4 1 1 12 5
51253668 9/27/2002 9:35:50 AM TABLE ACCESS BY LOCAL INDEX ROWID DMGMT FCBR_DOC_PART 2 ANALYZED 6 5 1 4758 1 108 1 12 5
51253668 9/27/2002 9:35:50 AM INDEX RANGE SCAN DMGMT FCBR_DOC_PART_REL_DATE NON-UNIQUE ANALYZED 7 6 1 88 1 1 12 5
51253668 9/27/2002 9:35:50 AM TABLE ACCESS BY INDEX ROWID DMGMT FCBR_BROKER 3 ANALYZED 8 4 2 1 2671 61433
51253668 9/27/2002 9:35:50 AM INDEX UNIQUE SCAN DMGMT FCBR_BRKRCODE_IDX UNIQUE ANALYZED 9 8 1 2671
51253668 9/27/2002 9:35:50 AM TABLE ACCESS BY INDEX ROWID DMGMT FCBR_DOC_TICKER 5 ANALYZED 10 3 2 3 3099901 55798218
51253668 9/27/2002 9:35:50 AM INDEX RANGE SCAN DMGMT FCBR_DOC_TICKER_KEY NON-UNIQUE ANALYZED 11 10 1 2 3099901
51253668 9/27/2002 9:35:50 AM INDEX RANGE SCAN DMGMT FCBR_DOC_TICKER_IDX NON-UNIQUE ANALYZED 12 2 2 3 1 11
51253668 9/27/2002 9:35:50 AM INDEX UNIQUE SCAN DMGMT FCBR_DOC_COUNTRY_CODE UNIQUE ANALYZED 13 2 3 2 1 10
51253668 9/27/2002 9:35:50 AM INDEX UNIQUE SCAN DMGMT FCBR_PRODENTKEY UNIQUE ANALYZED 14 2 4 1 1 11
51253668 9/27/2002 9:35:50 AM NESTED LOOPS 15 2 5 3 2 38
51253668 9/27/2002 9:35:50 AM INDEX RANGE SCAN DMGMT FCBR_DOC_ENT_IDX UNIQUE ANALYZED 16 15 1 3 2 22
51253668 9/27/2002 9:35:50 AM INDEX UNIQUE SCAN DMGMT FCBR_MLENTKEY UNIQUE ANALYZED 17 15 2 971 7768
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 02:12:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
