Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production "CORE 10.2.0.1.0 Production" TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
1)can the optimizer be told a column is ordered alphabetically and hence avoid full table scanHope this link is useful :
2)can we partition a table alphabeticallyThe best solution to do this is RANGE Partition .
PARTITION BY RANGE COLUMNS(fname) ( PARTITION p_1 VALUES LESS THAN ('A'), PARTITION p_2 VALUES LESS THAN ('B'), PARTITION p_3 VALUES LESS THAN ('C'), ....... );
Even if there were an index or primary key that would not affect the data itself. Rows in a table are like balls in a basket; there is no 'first' row.
CREATE TABLE FOO_TEST ( EVENT_TIME VARCHAR2(18 BYTE), EVENT_TYPE NUMBER(38) ) PARTITION BY RANGE (EVENT_TIME) ( PARTITION EVT_2011 VALUES LESS THAN ('20120101000000') )
Of course it won't work. Don't you see that extra box-looking character you put at the end ot it? If you edit your original post and remove that character the link works just fine.i did not put that character
The optimizer alreadys knows about 'returning results fast' - that is what it is designed to do. It does that best when it has all of the relevant information in the form of current stats and possibly histograms.i gather stats after materialized view refresh
If the query uses a partition key value in an equality predicate pruning will be done.even i dont.
I still don't understand why you need to partition it at all.
You should quit focusing on the solution you want to use and refocus on the problem.i am focusing on the problem and possible solution*s*