hi, I ran the 'analyze table...' command on a test table, but how do I see the analyzed results? Thanks@!
SQL> analyze table test_table compute statistics;
Table analyzed.
SQL>
Printable View
hi, I ran the 'analyze table...' command on a test table, but how do I see the analyzed results? Thanks@!
SQL> analyze table test_table compute statistics;
Table analyzed.
SQL>
user_tables, dba_tables, tabs
Maybe a better question is "What results are you expecting to see?"
Thanks!
It seems that these data are static. Unlike the 'explain plan' command, the 'analyze table...' command doesn't really help me to optimize my query.
Huh? Explain plan and Analyze table are two different commands. "EXPLAIN PLAN" explains how the optimizer will execute your query. "ANALYZE TABLE" will compute statistics on your table which the optimizer will use to make it's choices. EXPLAIN PLAN without fresh statistics from ANALYZE will be useless (more or less).Quote:
Originally posted by thg
Thanks!
It seems that these data are static. Unlike the 'explain plan' command, the 'analyze table...' command doesn't really help me to optimize my query.
marist89,
I just realized that the 'explain plan' uses the statistics from 'analyze table' & 'analyze indexes'. Does that mean if I don't ran the 'analyze...' commands frequently, my query will become very slow?
Here is one of the 'explain plan' results from my query, is my query optimized?
Below is a simplified version of my query, basically it scans through the table to find entries that are before certain date. I've defined a cluster index on (id, update_date), but it seems that it still perform full table scan on my 'security_ts_coupon' table
Quote:
// sample query
select t1.id, t1.update_date from security_ts_coupon t1 where t1.id in (12,23,43...) and t1.update_date < ?;
Quote:
// 'explain plan' result
ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST CARDINALITY
--------- ------------------------------ -------------------- -------------------- -------------------- --------- -----------
0 SELECT STATEMENT CHOOSE 12 1
1 FILTER
2 NESTED LOOPS OUTER 12 1
3 NESTED LOOPS OUTER 9 1
4 NESTED LOOPS OUTER 7 1
5 NESTED LOOPS OUTER 6 1
6 NESTED LOOPS OUTER 5 1
7 NESTED LOOPS OUTER 4 1
8 NESTED LOOPS OUTER 3 1
9 TABLE ACCESS BY INDEX ROWID SECURITY ANALYZED 2 1
10 INDEX UNIQUE SCAN PK_SECURITY ANALYZED 1 1
11 TABLE ACCESS BY INDEX ROWID SECURITY_MORTGAGE ANALYZED 1 1264
12 INDEX UNIQUE SCAN PK_SECURITY_MORTGAGE ANALYZED 1264
13 TABLE ACCESS BY INDEX ROWID SECURITY_BOND_SWAP ANALYZED 1 1296
14 INDEX UNIQUE SCAN PK_SECURITY_BOND_SWA ANALYZED 1296
P
15 TABLE ACCESS BY INDEX ROWID SECURITY_ARM ANALYZED 1 111
16 INDEX UNIQUE SCAN PK_SECURITY_ARM ANALYZED 111
17 TABLE ACCESS BY INDEX ROWID SECURITY_OPTION ANALYZED 1 93
18 INDEX UNIQUE SCAN PK_SECURITY_OPTION ANALYZED 93
19 TABLE ACCESS FULL SECURITY_TS_RATING ANALYZED 1 1
20 TABLE ACCESS FULL SECURITY_TS_COUPON ANALYZED 2 1429
21 TABLE ACCESS BY INDEX ROWID SECURITY_TS_FACTOR ANALYZED 3 1474
22 INDEX RANGE SCAN PK_SECURITY_TS_FACTO ANALYZED 2 1474
R
23 SORT AGGREGATE 1
24 INDEX FAST FULL SCAN PK_SECURITY_TS_FACTO ANALYZED 2 2
R
25 SORT AGGREGATE 1
26 INDEX FAST FULL SCAN PK_SECURITY_TS_COUPO ANALYZED 2 2
N
27 rows selected.
Quote:
Originally posted by thg
marist89,
I just realized that the 'explain plan' uses the statistics from 'analyze table' & 'analyze indexes'. Does that mean if I don't ran the 'analyze...' commands frequently, my query will become very slow?
Not necessarily. If the distribution of your data does not change or if the volume does not change, you don't have to analyze as frequently. However, if your data changes rapidly, you should analyze more frequently.
Depends. Sometimes a Full Scan is preferable to an index hit. If you have indexes on the tables that are full scanned, I would force the index usage and see if the response time decreases.Quote:
Here is one of the 'explain plan' results from my query, is my query optimized?