-
reducing cost of table query
I have a table query which does SELECT on table A twice. The query is as follows:
SELECT * from A where (ID, NAME) in (
SELECT ID, max(NAME) from A group by ID)
I have a composite index, INN, on (ID, NAME) as well as a single index, NN, on NAME.
However, when i ran explain plan on this query, it gives the following:
SELECT STATEMENT Optimizer Mode=ALL_ROWS (rows=2, cost=13457)
TABLE ACCESS BY INDEX ROWID A (rows=2, bytes=584, cost=2)
NESTED LOOPS (rows=2, bytes=656, cost=13457)
VIEW (rows=1 M, bytes=43 M, cost=1186)
HASH GROUP BY (rows=1, bytes=43 M, cost=1186)
INDEX FAST FULL SCAN INN_IDX (rows=1 M, bytes=43 M, cost=971)
INDEX RANGE SCAN INN_IDX (rows=1, cost=1)
Is there any way i can improve the query by utilizing my present indexes?
It seems that the line 'SELECT * from...' has the highest cost and doesn't use any index in its scan. Correct me if i'm wrong.
Also, is there a specific cost range which we should have to ensure optimal performance?
Thanks!
-
TABLE ACCESS BY INDEX ROWID A
should tell you that index on A is used. The optimizer executes the subquery using fast full index scan on the composite index, and then joins the results to the table A, using the composite index again, since you join on both columns (id and name)
NL join is used, probably because of the small number of the returned rows.
To summarize I see nothing wrong with this query, the plan seems simple and good
-
Thanks Bore!
Does that mean that this SQL performance is acceptable despite its high cost value? In this case, can we just ignore the cost value?
-
Anyway, the query was re-written and the explain plan was run through as well...
In this case, the cost is much lower. So, is it correct to say that this will have better performance than the previous query in terms of I/O and time?
PHP Code:
SELECT STATEMENT Optimizer Mode=ALL_ROWS (rows=2, cost=11)
TABLE ACCESS BY INDEX ROWID A (rows=2, bytes=584, cost=2)
NESTED LOOPS (rows=2, bytes=656, cost=11)
VIEW (rows=80, bytes=2 K, cost=8)
HASH GROUP BY (rows=1, bytes=5 K, cost=8)
NESTED LOOPS (rows=80, bytes=5 K, cost= 7)
INDEX RANGE SCAN SYS_C0020790 (rows=1, bytes=30, cost=2)
INDEX RANGE SCAN INN_IDX (rows=904, bytes=31 K, cost=5)
INDEX RANGE SCAN INN_IDX (rows=1, cost=1)
Thanks!!!
-
In the latest explain plan it's using index range scan where in the former plan it was using Index fast full scan. I think the later plan looks good. What about response time. Seems like the later one should have better response time.
-
Response time? hm.. haven't thought about it.
Do you have any idea how to find the estimated response time? Or some link which i can refer to?
Thanks a lot!
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
dont believe less cost means faster, not true at all
-
The inital query takes 6 sec.. After modifications, it only took 15 ms...
If 'less cost doesn mean faster', does that mean that we can only use cost to determine the I/O and not execution time?
Thanks so much!!
-
Originally Posted by gxangel
The inital query takes 6 sec.. After modifications, it only took 15 ms...
If 'less cost doesn mean faster', does that mean that we can only use cost to determine the I/O and not execution time?
Thanks so much!!
Not even I/O
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
|