| What influences cost? [message #65754] |
Tue, 21 December 2004 11:08  |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
I have a table - "folder". Two columns of importance are folder_id and folder_path. I have an index on the column "folder_path". The cost of one of the queries is significantly higher than the other, and I have no clue why. Can anyone give me an idea of how I can determine why the cost is higher? I can provide more information if you require.
Query 1:
select folder_id from folder
where folder_path='/TKB/Compliance/International U.S.'
Cost 1: 82
Query 2:
select folder_id from folder
where folder_path='/TKB/Compliance/Federal U.S.'
Cost 2: 2
The table has approximately 217000 entries in it. About 183000 of the rows have null for the folder_path and the other 34000 have a unique value. Your help is much appreciated.
|
|
|
|
|
|
|
|
|
|
| Re: What influences cost? [message #65761 is a reply to message #65758] |
Wed, 22 December 2004 03:34  |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
|
This is exactly my problem. These two queries are part of a larger query. The larger queries are generating two different query plans. These two different query plans I believe are partly based on the cost of these queries I have posted. It seems to me that the cost of these two queries I have posted should be close, if not identical, as the column used in the where clause is in an indexed column. Am I not correct?
|
|
|
|
| Re: What influences cost? [message #65762 is a reply to message #65761] |
Wed, 22 December 2004 03:59 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>. It seems to me that the cost of these two queries I have posted should be close, if not identical, as the column used in the where clause is in an indexed column.
NO.
Not a must.
All becuase a column is indexed it doesnt mean that the cost should be high or low .
It actually depends on the number of rows scanned.
If the index is used in your query, it may be a litter faster.
There are many reason why the query may not use an index.
and sometimes ( if there is too many rows to be returned)
CBO will not use an index , becuase it is much efficient to have full table scan.
So first update the statistics of your table.
then look into execution plan.
Please have a look in this thread
http://www.orafaq.com/forum/t/23478/0/
and the followup thread by Frank
http://www.orafaq.com/forum/t/23478/0/
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
| Re: What influences cost? [message #65763 is a reply to message #65762] |
Wed, 22 December 2004 04:08 |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
I updated the stats and here are the query plans. As you can see, the query plans are the same - but the cost and Bytes differ significantly. Any insight?
/* Start : Plan and Statistics for slow query */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82 Card=100688 Bytes=3322704)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=82 Card=100688 Bytes=3322704)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=43 Card=15901)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
226 bytes sent via SQL*Net to client
318 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/* End : Plan and Statistics for slow query */
/* Start : Plan and Statistics for fast query */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=200 Bytes=6600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=2 Card=200 Bytes=6600)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=3 Card=32)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
226 bytes sent via SQL*Net to client
318 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/* End : Plan and Statistics for fast query */
|
|
|
|
|
|
| Re: What influences cost? [message #65765 is a reply to message #65762] |
Wed, 22 December 2004 04:19 |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
Hello,
I created a copy of the table by doing a create table as select, and then created an index on the newly created table. I analyzed the table and the index and these are my query plans for the copied table... By this, would you think the root problem with the original table lies in the table or in the index? This is the execution plan that is reported for BOTH queries - which is how I would expect it to work for the original table.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=198)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FOLDER' (Cost=2 Card=6 Bytes=198)
2 1 INDEX (RANGE SCAN) OF 'FOLDER_PATH_INDEX' (NON-UNIQUE) (Cost=3 Card=1)
|
|
|
|
|
|
|
|
| Re: What influences cost? [message #65768 is a reply to message #65767] |
Wed, 22 December 2004 04:33 |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
|
Could my query benefit from reorganizing the table or resequencing the rows? As I mentioned in one of my previous postings, if I create a copy of the table and index the copy, the results return quite quickly in both cases.
|
|
|
|
|
|
| Re: What influences cost? [message #65770 is a reply to message #65769] |
Wed, 22 December 2004 04:42 |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
These are the results of the fast and slow queries on both the original and copied tables. As you can see, the execution plan for both queries in the copied table are identical.
/* Original Table Execution Plan (Slow Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82 Card=100688 Bytes=3322704)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=82 Card=100688 Bytes=3322704)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=43 Card=15901)
/* Original Table Execution Plan (Fast Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=200 Bytes=6600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DM_FOLDER_R' (Cost=2 Card=200 Bytes=6600)
2 1 INDEX (RANGE SCAN) OF 'D_1F000C4F80000015' (NON-UNIQUE) (Cost=3 Card=32)
/* Copied Table Execution Plan (Fast Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=97 Bytes=3201)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FOLDER' (Cost=2 Card=97 Bytes=3201)
2 1 INDEX (RANGE SCAN) OF 'FOLDER_PATH_INDEX' (NON-UNIQUE) (Cost=3 Card=15)
/* Copied Table Execution Plan (Slow Query) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=97 Bytes=3201)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FOLDER' (Cost=2 Card=97 Bytes=3201)
2 1 INDEX (RANGE SCAN) OF 'FOLDER_PATH_INDEX' (NON-UNIQUE) (Cost=3 Card=15)
|
|
|
|
|
|
| Re: What influences cost? [message #65773 is a reply to message #65771] |
Wed, 22 December 2004 04:56 |
Shane Kaszyca
Messages: 16
Registered: May 2003
|
Junior Member |
|
|
You know what, I analyzed the index but not the table. Now that I analyzed the table it is working. The execution plans appear to be working as expected now. I am in your debt. Thank you and happy holidays.
Shane Kaszyca
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
