|
-
Originally posted by DaPi
I don't see much point in re-wrting the query in detail, since we aren't quite sure what it actually is!
"Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query."
..hmm i quite really didnt see this part in Anand's Later post.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
I dont see any reason why 1st and 4th should not take better advantage of existing composite index. 
Yes, they should use the index, but only in so far as the index will (I think) find the rowid's of ALL the rows with COMPANY_ID = 8170926 because there is no condition on the second column in the index, PAYMENT_CLEARED (9i might do better, but this is 8.1.7). The 2nd & 3rd can be more selective because PAYMENT_CLEARED (and SHIPMENT_STATUS in the 3rd) are part of the condition.
-
many times Oracle rewrites OR into UNION ALL
-
Originally posted by DaPi
Yes, they should use the index, but only in so far as the index will (I think) find the rowid's of ALL the rows with COMPANY_ID = 8170926 because there is no condition on the second column in the index, PAYMENT_CLEARED (9i might do better, but this is 8.1.7). The 2nd & 3rd can be more selective because PAYMENT_CLEARED (and SHIPMENT_STATUS in the 3rd) are part of the condition.
Ok, lemme give u an example of how best you can get from an index...
Code:
WW04_PRO> Select Column_Position, Index_Name, Table_Name, Substr(Column_Name, 1, 30) from dba_ind_columns
2 where table_name = 'YTD_MTD_SHIPMENTS';
COLUMN_POSITION INDEX_NAME TABLE_NAME SUBSTR(COLUMN_NAME,1,30)
-------------------- ------------------------------ ------------------------------ ------------------------------
1 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS FORDATE
2 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS REGION_CD
3 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS ORG_LEVEL_NO
4 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS PH_LEVEL_NO
5 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS PRODUCT_TYPE
6 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS PIN
7 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS ORG_CD
8 PK_YTD_MTD_SHIPMENTS YTD_MTD_SHIPMENTS COMPANY_CD
8 rows selected.
WW04_PRO> ed
Wrote file afiedt.buf
1* Select Count(Distinct REGION_CD) from ytd_mtd_shipments
WW04_PRO> /
COUNT(DISTINCTREGION_CD)
------------------------
3
Elapsed: 00:00:01.01
WW04_PRO> ed
Wrote file afiedt.buf
1* Select Count(Distinct Org_Level_No) from ytd_mtd_shipments
WW04_PRO> /
COUNT(DISTINCTORG_LEVEL_NO)
---------------------------
5
Elapsed: 00:00:01.01
WW04_PRO> ed
Wrote file afiedt.buf
1* Select Count(Distinct Ph_Level_No) from ytd_mtd_shipments
WW04_PRO> /
COUNT(DISTINCTPH_LEVEL_NO)
--------------------------
9
Elapsed: 00:00:02.00
WW04_PRO> Select Count(Distinct Product_Type) from ytd_mtd_shipments;
COUNT(DISTINCTPRODUCT_TYPE)
---------------------------
7
Elapsed: 00:00:01.01
WW04_PRO> Select * from YTD_MTD_SHIPMENTS where FORDATE = '18-DEC-03' and PIN = 'DA236A#ABA';
FORDATE RE O P PR PIN ORG_CD
--------- -- - - -- ------------------ -----------------
18-DEC-03 AM 0 8 CM DA236A#ABA CORP
18-DEC-03 AM 1 8 CM DA236A#ABA AMERICAS
18-DEC-03 AM 2 8 CM DA236A#ABA LATIN AMERICA
18-DEC-03 AM 3 8 CM DA236A#ABA NMCA
18-DEC-03 AM 4 8 CM DA236A#ABA 18SP
Elapsed: 00:00:09.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=13 Bytes=1365)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'YTD_MTD_SHIPMENTS' (Cost=14 Card=13 Bytes=1365)
2 1 INDEX (RANGE SCAN) OF 'PK_YTD_MTD_SHIPMENTS' (UNIQUE) (Cost=9 Card=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2173 consistent gets
1431 physical reads
6134756 redo size
2472 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
WW04_PRO> ed
Wrote file afiedt.buf
1 Select * from
2 (Select * from YTD_MTD_SHIPMENTS where FORDATE = '18-DEC-03' and rownum > 0)
3* Where PIN = 'DA236A#ABA'
WW04_PRO> /
FORDATE RE O P PR PIN ORG_CD
--------- -- - - -- ------------------ ----------------------
18-DEC-03 AM 0 8 CM DA236A#ABA CORP
18-DEC-03 AM 1 8 CM DA236A#ABA AMERICAS
18-DEC-03 AM 2 8 CM DA236A#ABA LATIN AMERICA
18-DEC-03 AM 3 8 CM DA236A#ABA NMCA
18-DEC-03 AM 4 8 CM DA236A#ABA 18SP
Elapsed: 00:00:23.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=136 Card=1347 Bytes=449898)
1 0 VIEW (Cost=136 Card=1347 Bytes=449898)
2 1 COUNT
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'YTD_MTD_SHIPMENTS'(Cost=136 Card=1347 Bytes=141435)
5 4 INDEX (RANGE SCAN) OF 'PK_YTD_MTD_SHIPMENTS' (UNIQUE) (Cost=9 Card=539)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
139182 consistent gets
2674 physical reads
0 redo size
2352 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
WW04_PRO>
See above how in the second case Index Range Scan is confined to just FORDATE col, & in first case how is it benifited even when you have PIN at the 6th position & you can expect that Index has to search at the worst 3*5*9*7 keys to find the pin.
I think there is something more than what we have been told to us by DOCS.
Abhay.
Last edited by abhaysk; 12-19-2003 at 06:34 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Abhay, which version are you running?
-
Originally posted by DaPi
Abhay, which version are you running?
9.2.0.4, but i dont think 8.x will do any bad....
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Version 9i has INDEX SKIP SCAN http://download-west.oracle.com/docs...mops.htm#51553 which did not exist before. I imagine that could be happening (even if the explain plan does not say so).
-
But the above eg is suerly not a skip scan for sure....
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Ok, Here goes for 8.x
Code:
U144> Select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Elapsed: 00:00:01.01
U144> select column_position, index_name, table_name, substr(column_name, 1,30) from dba_ind_columns
2 where table_name = 'RPT_PIT_TRANS_DATA' and index_name like 'PK%';
COLUMN_POSITION INDEX_NAME TABLE_NAME SUBSTR(COLUMN_NAME,1,30)
-------------------- ------------------------------ ------------------------------ ------------------------------
1 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA PLANT_CD
2 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA SUPPLIER_NO
3 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA MATERIAL_NO
4 PK_RPT_PIT_TRANS_DATA RPT_PIT_TRANS_DATA EFFECTIVE_DT
Elapsed: 00:00:02.09
U144> Set autotrace on
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and MATERIAL_NO = '207074-101');
COUNT(*)
--------------------
308
Elapsed: 00:00:15.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=254 Card=1 Bytes=15)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_PIT_TRANS_DATA' (Cost=254 Card=2 Bytes=30)
3 2 INDEX (RANGE SCAN) OF 'AK4_RPT_PIT_TRANS_DATA' (NON-UNIQUE) (Cost=5 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
788 consistent gets
784 physical reads
0 redo size
369 bytes sent via SQL*Net to client
390 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and rownum > 0)
2 Where MATERIAL_NO = '207074-101';
COUNT(*)
--------------------
308
Elapsed: 00:01:06.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=1026 Card=131038 Bytes=1441418)
3 2 COUNT
4 3 FILTER
5 4 INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=131038 Bytes=1965570)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13973 consistent gets
13931 physical reads
1404 redo size
369 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and EFFECTIVE_DT = '20011112');
COUNT(*)
--------------------
8597
Elapsed: 00:01:01.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=66 Bytes=792)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13976 consistent gets
13932 physical reads
1456 redo size
369 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
U144> ed
Wrote file afiedt.buf
1 Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and rownum > 0)
2* Where EFFECTIVE_DT = '20011112'
U144> /
COUNT(*)
--------------------
8597
Elapsed: 00:01:07.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=1026 Card=131038 Bytes=786228)
3 2 COUNT
4 3 FILTER
5 4 INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=131038 Bytes=1572456)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13974 consistent gets
13930 physical reads
1464 redo size
369 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
U144>
You can see above only in last 2 cases it will be of no importance as it has to scan all the missed out keys obtained from COL postion 2 and 3...But If i see anandkl's composite key, theres only 2 cols that will not be used in sub qry 1 and 4 but again these cols in where clause are not at the end...and more over the card of shipment_status and PAYMENT_CLEARED are just 2 each so at the most you will be scanning 4 times more without including these 2 cols and with including...so i expect range scan will do better that not using.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi Friends,
Thank you all for the inputs, i am pasting the stats asked by Tamilselvan
Statistics
----------------------------------------------------------
889 recursive calls
121 db block gets
723827 consistent gets
693231 physical reads
700 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
anandkl
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote