Tom,
I don't know whether I have made my question clear enough or not. The table A as referred to in my question has a date field (update_date) and contains 2 million rows for a single update_date. My history table B is supposed to contain data from A, which will be populated from A on a daily basis and the data retention need for the history table is 30 months. Now I am not sure from your answer about whether I should create a partition using update_date as key or not on table B. Are you suggesting soem other field for partitioning key? Also please can you discuss a little on local indexes and how to handle that in this situation. Due to tablespace constraint I have to create table B on another database. Now can you please clear me the following lines from your answer. I did n't understand what you meant by 'switcher-roo'

"Now, what we do is transport the tablespace with T (contains the old data from BIG_TABLE) and attach it to the other database and just do the switcher-roo over "
there.

Thanks very much for your help.

This is follow up of my earlier mail on this subject. The requirement has suddenly changed and now it is like this.

I have to keep 12 months of data in the table. The table has to be partitioned on update_date( one partition every day). But for manageability purposes the data should be in 4 different tablespaces which should not be hard coded. Same is for the indexes that is they should be in different tablespaces and should not be hardcoded. Index tablespaces are different from data tablespaces.

CREATE TABLE A
( COL1
COL2
....
UPDATE_DATE )
PARTITION BY RANGE ( UPDATE_DATE)
( PARTITION A_part_20021028 VALUES LESS THAN TO_DATE(' 2002-10-28', 'yyyy-mm-dd')
TABLESPACE tsa ,
PARTITION A_part_20021029 VALUES LESS THAN TO_DATE(' 2002-10-29', 'yyyy-mm-dd')
TABLESPACE tsa ,
PARTITION A_part_20021030 VALUES LESS THAN TO_DATE(' 2002-10-30', 'yyyy-mm-dd')
TABLESPACE tsa ,
......
PARTITION A_part_20030130 VALUES LESS THAN TO_DATE(' 2003-01-30', 'yyyy-mm-dd')
TABLESPACE tsb,
PARTITION A_part_20030430 VALUES LESS THAN TO_DATE(' 2003-04-30', 'yyyy-mm-dd')
TABLESPACE tsc,
PARTITION A_part_20030730 VALUES LESS THAN TO_DATE(' 2003-07-30', 'yyyy-mm-dd') TABLESPACE tsd)

Can you please give me a quick example on this.

Tom,

Can you please reponse to my questions I requested earlier. Below is the link.
Thanks in advance

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6050339907954,%7Baparajita%7D <code>

Tom,

I refer to the link below.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1238800184155,%7Bpartitions%7D <code>

"A partition exchange is simply an exchange of segments -- no movement of data takes place. It is a data dictionary update". That means if I have a partition P1 of a partitioned table A in tablespace ta, and I exchange this partition with a table B in tablespace Tb, then Partition P1 of Table A will now reside in tablespace Tb and table B will reside in tablespace ta. with refernce to local index what is needed to be done so that index tablespaces are swapped like this. Is there any other mechanism aparat from
"The ALTER INDEX...REBUILD PARTITION statement rebuilds one partition of an index. It cannot be used on a composite-partitioned table. At the same time as you recreate the index, you can move the partition to a new tablespace or change attributes"
How otherwise you meet the criteria of creating a partition P1 of table A residing in tablespace t1, index of partition P1 in tablespace ti1 and so on... Pleases elaborate.

Once again many thanks for your time.

Tom,

Many Thanks for the prompt response.
This is specific to oracle 8.1.7 .

I just want to know how you can create local index so that each index partition resides in different tablespaces. I am still not clear whether I have made my point clear or not. Please reply me back if my question is not clear.

Here is a trick I use often to figure out the full syntax of something when I don't feel like reading the SQL Reference guide.  I use exp/imp on a "minimum example".  Exp/Imp is very "verbose"

Here you go:

ops$tkyte@ORA920.US.ORACLE.COM> CREATE TABLE t
  2  (
  3    x int ,
  4    collection_year int
  5  )
  6  PARTITION BY RANGE (COLLECTION_YEAR) (
  7    PARTITION PART_95 VALUES LESS THAN (1996) ,
  8    PARTITION PART_96 VALUES LESS THAN (1997) ,
  9    PARTITION PART_01 VALUES LESS THAN (MAXVALUE)
 10  )
 11  ;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create index t_idx on t( x ) local;

Index created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> host exp userid=/ tables=t

Export: Release 9.2.0.1.0 - Production on Sat Nov 2 12:25:20 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T
. . exporting partition                        PART_95          0 rows exported
. . exporting partition                        PART_96          0 rows exported
. . exporting partition                        PART_01          0 rows exported
Export terminated successfully without warnings.

ops$tkyte@ORA920.US.ORACLE.COM> host imp userid=/ full=y show=y

Import: Release 9.2.0.1.0 - Production on Sat Nov 2 12:25:20 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
 "CREATE TABLE "T" ("X" NUMBER(*,0), "COLLECTION_YEAR" NUMBER(*,0))  PCTFREE "
 "10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS" LOGGING PARTITION "
 "BY RANGE ("COLLECTION_YEAR" )  (PARTITION "PART_95" VALUES LESS THAN (1996)"
 "  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREEL"
 "ISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS, PARTITION "
 ""PART_96" VALUES LESS THAN (1997)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
 "S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER"
 "S" LOGGING NOCOMPRESS, PARTITION "PART_01" VALUES LESS THAN (MAXVALUE)  PCT"
 "FREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS "
 "1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS )"
. . skipping partition "T":"PART_95"

. . skipping partition "T":"PART_96"

. . skipping partition "T":"PART_01"

 "CREATE INDEX "T_IDX" ON "T" ("X" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGG"
 "ING LOCAL(PARTITION "PART_95"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(I"
 "NITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING, PAR"
 "TITION "PART_96"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
 "FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING, PARTITION "PART_"
 "01"  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F"
 "REELIST GROUPS 1) TABLESPACE "USERS" LOGGING ) "
Import terminated successfully without warnings.

ops$tkyte@ORA920.US.ORACLE.COM>


that shows the full verbose syntax for doing this, with tablespaces and all. 

Tom,
Thanks for providing such a detailed example. I should not have continued this discussion further but I really need this point clear. This is fine if we know the tablespaces for indexes (for differnt partitions ) at the time of creating the local index. But that is not the case with my development requirement. As the DBA insists that he is not going to create all the future tablespaces for storing the data/ indexes at this point of time. They will be created later as and when they are required. He thinks that these tablespaces should be parameter driven of number of months of data etc.
Could you please tell me what should be the way to create the local index in that situation. Do you think there is no other alternative than using "The ALTER INDEX...REBUILD PARTITION" for each time a partition is added so that the index patrtition is residing in a specific tablespace. Is it at all a good idea or I have to think something else.

ops$tkyte@ORA920.US.ORACLE.COM> CREATE TABLE "OPS$TKYTE"."T" ("X" NUMBER(*,0), "COLLECTION_YEAR"
  2  NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE
  3  "USERS" LOGGING PARTITION BY RANGE ("COLLECTION_YEAR" ) (PARTITION
  4  "PART_95" VALUES LESS THAN (1996) PCTFREE 10 PCTUSED 40 INITRANS 1
  5  MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
  6  TABLESPACE "USERS" LOGGING NOCOMPRESS, PARTITION "PART_96" VALUES
  7  LESS THAN (1997) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  8  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
  9  "USERS" LOGGING NOCOMPRESS ) ;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE INDEX "OPS$TKYTE"."T_IDX" ON "T" ("X" ) PCTFREE 10 INITRANS 2
  2  MAXTRANS 255 LOGGING LOCAL(PARTITION "PART_95" PCTFREE 10 INITRANS 2
  3  MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
  4  TABLESPACE "USERS" LOGGING, PARTITION "PART_96" PCTFREE 10 INITRANS 2
  5  MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
  6  TABLESPACE "USERS" LOGGING ) ;

Index created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table t add partition part_97 values less than (1998) tablespace tools
  2  /

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select 'TABLE', partition_name, tablespace_name
  2    from user_tab_partitions
  3   where table_name = 'T'
  4   union all
  5  select 'TABLE', partition_name, tablespace_name
  6    from user_ind_partitions
  7   where index_name = 'T_IDX'
  8  /

'TABL PARTITION_NAME                 TABLESPACE_NAME
----- ------------------------------ ------------------------------
TABLE PART_95                        USERS
TABLE PART_96                        USERS
TABLE PART_97                        TOOLS
TABLE PART_95                        USERS
TABLE PART_96                        USERS
TABLE PART_97                        TOOLS

6 rows selected.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter index t_idx rebuild partition part_97 tablespace xdb;

Index altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select 'TABLE', partition_name, tablespace_name
  2    from user_tab_partitions
  3   where table_name = 'T'
  4   union all
  5  select 'TABLE', partition_name, tablespace_name
  6    from user_ind_partitions
  7   where index_name = 'T_IDX'
  8  /

'TABL PARTITION_NAME                 TABLESPACE_NAME
----- ------------------------------ ------------------------------
TABLE PART_95                        USERS
TABLE PART_96                        USERS
TABLE PART_97                        TOOLS
TABLE PART_95                        USERS
TABLE PART_96                        USERS
TABLE PART_97                        XDB

6 rows selected.


would be the way to go <b>if you really truly think you need, want or desire</b> the index to be in a separate tablespace.  Me, I would keep it in the same tablespace probably -- makes the tablespace self contained (easier to transport or unplug).  There isn't any real performance benefit to separating these things or anything.  I would probably just let them be together.

Since the index partition is empty, the rebuild it instantaneous. 

Tom,
Thanks so much for your help with this. I have read few discussions on this site about the issue of keeping the index and data in separate tablespaces and I definitely appreciate your suggestions of keeping them in same tablespaces but some people dont think very much logically. That is the reason of keeping them in separate tablespaces.

I am partitioning on update_date but most of the queries asked on this table will be something like

select * from table A
where col1 =to_date('03-nov-2002', 'dd-mon-yyyy')
and col2 = 'A'

or

Select * from table A
where col2='A'

Here col1 is date field and let's say with 400,000 distinct values and col2 with three distinct values in 2 million rows table. What kind of indexes should be preferred.
1)Local nonprefixed index on col1 and local nonprefixed bitmap index on col2
2) or local nonprefixed composite index on (col1, col2)
or something else.

Your suggestion will be very much appreciated.

Tom,

First of all thanks for your help.
I have taken note of your suggestion for a global index. But I have two points to understand
i) It may not be possible to keep each index partition in separate tablespace in case of global index - and that is why my choice is restricted to use local index
ii)It is a case of rolling window where I may need to rebuilt the global index - which may be time consuming.

My question is as I don't know exactly what kind of queries will be running on this partition table - is it advisable to the users for the inclusion of update_date (partition_key) in all their queries so that partition elimination takes palce. Can you please suggest.

I'm a developer who's been asked to modify the structure of a database and I have some doubts (concept problems indeed).

I'm working with version 8.1.7 and I have a table that must be partitioned by range on a date column. Initially the table was thought to have a primary key on other column, I think the index associated with that column should also be partitioned. Am I right?
More questions:
Should the index be local or global?
Can I create the partioned index without dropping the primary key? If not, how can I force the old pk column to be unique after creating a non unique partioned index on it?

Apart from that, the table has 2 foreign keys on fields that are also unique and has 2 unique indexes associated with them. Should these indexes also be partioned as the one of the primary key?

Any advice will be welcome. Thanks in advance.

I don't have any of your books. Can you tell what their titles are?

Hi Tom,

I have been trying to get an opportunity to ask you a question for the last few weeks... but seems that you are
utterly swamped with queries at the moment, thus not able to take any more!

Since this thread is relevant to my question, I can only hope that your cursory glance would fall on this someday
:-)

Anyway... here goes.

I have a *big* table which is being used in the following
manner:

1. Every 2 hours data is loaded into it.

2. There are 2 materialized views on it, which get FAST
refreshed. The refresh query for MV1 groups by
various columns, pertinent ones being:
a) Day_Of_Week
b) Hour_Of_Day
c) Week_Of_Year
d) Service_number
e) Year
The second MV uses a Group by but does not use any "time
parameters" for the grouping (unlike MV1).

Of course, both these MV's get refreshed every 2
hours (on commit)

3. There is another script run every night to delete
records from this table which are more than 100 days
old. So basically this table will contain only 100
days worth of data at any given time.

4. Some queries from the application directly query the
big table based on service_number column (and do not
group by).

(The service_number can be thought of as a telephone
number)

The table has grown to 25GB and we really need to partition it!!

I wanted your opinion about which columns could be used for the partitioning?
for eg: 24 (hours of day) * 7 (day of week)

Should there be a global index on the service_number?

Any thoughts would help.

Regards,
Sandeep

Hi Tom,

Thanks for responding!

Apologies for not providing that necessary info.

>> well, what is the goal behind your using partitioning in
this case.
--> a) one lame excuse... table is big.. 25GB+
doing a count(*) or even trying to
analyze the table is virtually impossible. Due to
this I can't even find out the state of the
indexes.. they are being hammered ~2 hours...
so I can only imagine that they are not in a *good
state*.

b) sometimes we have an "Invalid ROWID error" while the
snapshots on the big table are getting refreshed.
This never happened before, i.e when the table was
small. This *could* be happening due to sheer size
of the table resulting in slowness and thus some
row deletion which takes place before the
refresh or something else I don't seem to understand
at the moment, to be honest.

c) With partitioning, we hope to make the refresh
of the child MV's quicker. The logic(?) being:
If a new data load is done into the big table, it
will only have data which is pertinent to a specific
hour, thus only that partition *should* be
touched... and provided we get our physics right..
we can partition the MV's in such a way that only
some partitions are touched thus speeding up the
whole "load-to-refresh" process.

d) Manageability/ Recovery.. in case this main table of
system gets *corrupted*.

>> what operation do you want to maximize?
a) We need to maximize the "load-to-refresh" process.
Currently trying to load even 200,000 records can
take anytime from 2 hrs to 4 hrs! (I have found
that when hotbackups are taking place during night
then this time tends to increase)
There is another issue with the loading itself.
Basically, its an INSERT /*+ PARALLEL */ big_table
INTO SELECT * FROM ... statement which calls some
PL/SQL functions (ugh) as well to *massage* the
data..
Anyway.. the major time still goes while
refreshing those MVs' (There is one more nested MV
at level 3)... So from my observation.. total time
taken for the "load-to-refresh" can be roughly
broken like this:
#1 INSERT big_table INTO SELECT * FROM
load_table - 20%
#2 DELETE FROM load_table WHERE id between :1
and :2 - 10%
#3 then COMMIT which causes: - 60%
FAST Refresh of MV1 (level 1)
FAST Refresh of MV2 (level 1)
FAST Refresh of MV3 (level 2)
#4 Deletion of all snapshot logs from - 10%

b) We also need to improve the process which deletes
records every night from this table and uses:
" WHERE date_field < ( SYSDATE - 100 ) "

>> what version do you have?
This is 8174 (on unix)

I know how you despise about partitioning being thought of as fast=true.. but in this case maybe there is a valid
reason (or desperation :-)?

Regards,
Sandeep

specifically:

Hi Tom,
We are thinking to go for the partitioning option. However, we are still using Rule Based Optimizer (RBO). Just had some questions with respect to this.....
1. Is it going to be worthwhile to go with the partitioning option while using the RBO.
2.If yes, could we store the table spaces on a remote machine (other than the one which holds the current database).
The reason for question 2. is because we want to control the existing database size and were initially planning to do this by moving the tables and required data into a seperate database. However, this would have involved additional database maintenance, hence we are exploring the partitioning option.

Please advise.
Thanks!

Hello Tom,
Thanks a lot for you response. We've been long thinking about going with the CBO. So just incase we do so sooner or later, wanted to know if my second question makes sense.....viz. Could we have tablespace segments stored remotely (or do we even have to implement RAC before we could do this)?

Regards

Dear Tom,
I have a problem with selecting rows from a partitioned Table.
Please see below.

SQL> create table part(rn number)
  2  partition by range(rn)
  3  (partition p1 values less than(100),
  4  partition p2 values less than(200),
  5  partition p3 values less than(maxvalue)
  6  );

Table created.

SQL> insert /*+append*/ into part select rownum from sys.col$ 

where rownum <= 300;

300 rows created.

SQL> commit;

Commit complete.

SQL> select table_name,partition_name from user_tab_partitions
  2  /

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
PART                           P1
PART                           P2
PART                           P3

SQL> select * from part(p1);
select * from part(p1)
                  *
ERROR at line 1:
ORA-00933: SQL command not properly ended 


SQL> select * from part (partition p1);
select * from part (partition p1)
                   *
ERROR at line 1:
ORA-00933: SQL command not properly ended 

How to select from a particular partition?
Please do reply.

 

Dear Tom,
Thanks for your reply and the provided link.But I have a difficulty when we create a table by
hash partitioning.How to select from a particular partition when we partition data by *HASH*?
Since they do not have a name,How to do that?
Could you please reply.I created a hash table partition as follows.


SQL> create table emp_hash
  2  partition by hash(deptno)
  3  partitions 4
  4  parallel
  5  as select * from emp;

Table created.


 

HI Tom

We have a cold backup done every night. We have partitions that get dropped every first saturday with a retention period of 3 months. In April, Dec partition got dropped. The business now wants the data to be archived in tape to just get that partition data.
I am thinking of using Export with Query to just get that partition data. Is there a better way to just dump a partition and retrieve it? If so, could you please help me with that?
Thanks & Regards

Dear Tom,

I am facing a problem extracting the metadata of a table which is partitioned. The partitions are range partition, which I can see from DBA_PART_TABLES. 

Please clarify my 2 doubts :

1. I wanted to know the range of values which have defined the partitioned. Which data dictionary will let me know this information. 

2. As a workaround, I thought of extracting the metadata of the table using DBMS_METADATA but it is giving me error.

select owner, table_name from dba_tables
where table_name='FINANCE_TBL';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
XYZ                            FINANCE_TBL

1 row selected.

select dbms_metadata.get_ddl('TABLE','FINANCE_TBL','XYZ') from dual;

SQL> /
ERROR:
ORA-31603: object "FINANCE_TBL" of type TABLE not found in schema "XYZ"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

What could be the problem ?

Thanks and Regards
Vivek

 

Dear Tom,

Oracle Documentation
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/parpart.htm#745 <code>

on Partitioned Indexes says that

"Oracle supports only range partitioning for global partitioned indexes. You cannot partition global indexes using the hash or composite partitioning methods."

I am unable to understand what it means.

I have a partitioned table which is hash partitioned and have created a Global Index on it. Hence the statement "you cannot partition global indexes using hash" is not clear. A Global Index is an Index which is similar to a normal index created on a normal table. i.e.one to one relation. Hence "Global Partitioned Index" keyword confuses. It is not partitioned and hence do not appear in dba_ind_partitions view also. Local Indexes are partitioned on partition keys.

If I am wrong, kindly can you explain me what the statement in Oracle Documentation states.

Thanks and Regards
Vivek

Dear Tom,

Trying to improve the performance by partioning the table. The table has ~500K records but most of the queries are running against only 2 categories (GCAT and HCAT) which has only 
****17K records out of ~500 records****.


Do you see partitioning the table (GCAT & HCAT into separate partition and the remaining into one separate partition) will give better performance?

Most of the queries are using predicate USER_CATEGORY in ('GCAT','HCAT')

SQL> select USER_CATEGORY,count(*) from profile group by USER_CATEGORY;
 
USER_CATEGORY                         COUNT(*)
---------------------------------------- ----------
BUSCA                                          8420
BUSHK                                         39688
BUSHKHANGSENG                                 12526
BUSSG                                           863
BUSUK                                        244558
BUSUS                                         18657
DOTCOM                                       135216
GCAT                                           5188
HCAT                                          12752
                                               4738
                                         ===========
                                         482639
                                         ===========

We are stiil with 8i, so I shoudl go with range partition like
PARTITION BY RANGE (user_category)
(
PARTITION bus VALUES LESS THAN ('E%')
TABLESPACE CRM_DATARW STORAGE (INITIAL 1M NEXT 1M),
PARTITION gcat_hcat VALUES LESS THAN ( MAXVALUE)
TABLESPACE CRM_DATARW STORAGE (INITIAL 1M NEXT 1M)
)

Do you see any issue/negative side here.
BTW, the table is replicated.

Expecting your valuable input.

Thanks in advance 

Dear Tom,

It is not doing FTS but look at the below query. It is doing
index scan but in order to get 1430 rows, it is doing 8174 LIOs. So we are thinking that GCAT & HCAT rows are mixed up with another category which are not required very often.

SQL> l
  1   select first_name,last_name,lastupdatedate,userstatusid from profileduser p,extendedattributes e
  2   where p.profileduserid=e.profileduserid
  3   and p.hsbc_user_category in ('HCAT','GCAT')
  4   and e.lastupdatedate >= sysdate -30
  5* and p.region =4049
SQL> 

Elapsed: 00:00:22.99
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=241 Card=745 Bytes=76735)
   1    0   HASH JOIN (Cost=241 Card=745 Bytes=76735)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PROFILEDUSER' (Cost=100 Card=745 Bytes=42465)
   3    2       INDEX (RANGE SCAN) OF 'PROFILEDUSER_IX05' (NON-UNIQUE) (Cost=4 Card=745) 
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'EXTENDEDATTRIBUTES' (Cost=139 Card=888 Bytes=40848) 
   5    4       INDEX (RANGE SCAN) OF 'EXTENDEDATTRIBUTES_IX03' (NON-UNIQUE) (Cost=5 Card=888)
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8174  consistent gets
         16  physical reads
          0  redo size
      75217  bytes sent via SQL*Net to client
      10976  bytes received via SQL*Net from client
         97  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       1430  rows processed

Business doesn't like the respons time of 22 secs. 

Dear Tom,

Here is the original application query and tkprof output.
Oracle 8i,2 node OPS,No Application Partitioning.

SELECT * FROM (
 SELECT  /*+ FIRST_ROWS */ e.userstatusid,
         P.processed_by,
         P.last_name,
         P.first_name,
        P.company_name,
         c.countryname,
         e.customerid,
         TO_CHAR(e.LASTUPDATEDATE,'dd MON, yy ')||'AT '||TO_CHAR(e.LASTUPDATEDATE,'hh24:mi ')||'GMT' formattedlastupdatedate,
         P.userid,
         business_country_id
 FROM     (SELECT * FROM PROFILEDUSER WHERE hsbc_user_category IN ('GCAT','HCAT')) P,
     (SELECT  customerid,userstatusid,profileduserid,lastupdatedate FROM EXTENDEDATTRIBUTES WHERE lastupdatedate >= SYSDATE-30) e,
    COUNTRIES  c
 WHERE     P.profileduserid= e.profileduserid
 AND     P.business_country_id = c.countryabbrev
 AND     p.processed_by in ('3')
and     p.region in ('4051')
and p.business_country_id='GB'
and e.userstatusid in (1,113)
ORDER BY e.LASTUPDATEDATE desc
)
WHERE ROWNUM <=20

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      3.72      14.95       1948       5259          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      3.76      14.98       1948       5259          0           0

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 180  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  COUNT STOPKEY 
      0   VIEW 
      0    MERGE JOIN CARTESIAN 
      1     NESTED LOOPS 
    833      TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES 
   2838       INDEX RANGE SCAN DESCENDING (object id 1174163)
    832      TABLE ACCESS BY INDEX ROWID PROFILEDUSER 
   1664       INDEX UNIQUE SCAN (object id 5840)
      0     SORT JOIN 
      0      TABLE ACCESS BY INDEX ROWID COUNTRIES 
      0       INDEX RANGE SCAN (object id 1131957)

 OBJECT_ID OBJECT_NAME              INDEX_NAME              COLUMN_NAME     POSITION
---------- ------------------------ ----------------------- --------------- ------
   1131957 COUNTRIES_IX01           COUNTRIES_IX01          COUNTRYABBREV   1
   1174163 EXTENDEDATTRIBUTES_IX03  EXTENDEDATTRIBUTES_IX03 LASTUPDATEDATE  1
      5840 PROFILEDUSER_PK          PROFILEDUSER_PK         PROFILEDUSERID  1
 
 


  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  rdbms ipc reply                              1713        0.04          0.79
  global cache freelist wait                   1798        0.11          2.20
  global cache cr request                       957        0.42          2.69
  file open                                       4        0.01          0.01
  db file sequential read                      1948        0.30          6.77
  SQL*Net message from client                     1        0.01          0.01





In case if you need more info:

SQL> select count(*) from profileduser;
  COUNT(*)
----------
    483980

SQL> select count(*) from extendedattributes;
  COUNT(*)
----------
     18028

SQL> select count(*) from countries;
  COUNT(*)
----------
     239




TABLE_NAME           INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
-------------------- ------------------------------ -------------------- ---------------
PROFILEDUSER         PROFILEDUSER_I01               HSBC_USER_CATEGORY                 1
PROFILEDUSER         PROFILEDUSER_I02               BUSINESS_COUNTRY_ID                1
PROFILEDUSER         PROFILEDUSER_IX01              SEARCH_FIRST_NAME                  1
PROFILEDUSER         PROFILEDUSER_IX02              SEARCH_LAST_NAME                   1
PROFILEDUSER         PROFILEDUSER_IX03              SEARCH_COMPANY_NAME                1
PROFILEDUSER         PROFILEDUSER_IX04              USERID                             1
PROFILEDUSER         PROFILEDUSER_IX04              PROFILEDUSERID                     2
PROFILEDUSER         PROFILEDUSER_FK10              CAMLEVELID                         1
PROFILEDUSER         PROFILEDUSER_PK                PROFILEDUSERID                     1
PROFILEDUSER         PROFILEDUSER_AK1               USERID                             1
PROFILEDUSER         PROFILEDUSER_IX05              REGION                             1
PROFILEDUSER         PROFILEDUSER_IX06              PROCESSED_BY                       1

Indexes on EXTENDEDATTRIBUTES
==============================
TABLE_NAME           INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
-------------------- ------------------------------ -------------------- ---------------
EXTENDEDATTRIBUTES   ATTRIBUTES_PK                  PROFILEDUSERID                     1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK01        COMPANYINCCOUNTRY                  1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK02        COMPANYSECTORID                    1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK06        INVESTORCLASSID                    1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK07        LANGUAGEID                         1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK08        TIMEZONEID                         1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK09        USERTIERID                         1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK03        USERSTATUSID                       1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK04        STAFFUSERDEPTID                    1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK10        USERTYPEID                         1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_IX01        CUSTOMERID                         1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_IX03        LASTUPDATEDATE                     1
 
 

Tom,

Sorry, I missed your another question.
The machines are not under heavy load and it has 14 CPU/Node.

We are ready to provide any further details.

Your help on this regard is highly appreciated.

Tom,

Really appreciated your effort and time to answer my question.

You advised me to add additional column to the indexes(object_id) 1174163,5840.

833 TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES
2838 INDEX RANGE SCAN DESCENDING (object id 1174163)
832 TABLE ACCESS BY INDEX ROWID PROFILEDUSER
1664 INDEX UNIQUE SCAN (object id 5840)

5840 is a Primary Key index.

Why does the index returned 1664 rows instead of 832 rows?

You said the number of rowid returned by index is 1664 and that will reduced by adding additional column to that index. Could you please explain little bit how the adding column is helping here.

Thanks in advance.

Hi Tom,
While creating big table you used the hint /*+ APPEND */. I saw somewhere the use of /*+ APPEND NOLOGGING PARAALEL */. Could you please mention which one to use, if I am building a table of about 20Million rows on 3CPU UNIX machine?
Thanks

Hi Tom,
Thanks for your response. Could you please correct me if my understanding is wrong.
The NOLOGGING option in the INSERT statement mentioned by me does not do anything. If I want to by-pass REDO in INSERT, I must alter table to NOLOGGING. Further, if DB is NOT in archive mode (I know you hate this), then I need not worry about altering table to NOLOGGING.
Regarding, PARALLEL hint, I have following parameters set for the instance :
parallel_adaptive_multi_user -> FALSE
parallel_automatic_tuning -> FALSE
parallel_max_servers -> 5
parallel_server -> FALSE
parallel_threads_per_cpu -> 2
I am using a data warehousing application.
I think all FALSE parameters mentioned above should be set to TRUE. Am I correct?
Also, If I must set parallel_max_servers=6 in order to use PARALLEL 6 hint. Is it correct?
Thanks

Dear Tom,

Really appreciated your help in the past to fix many performance issue through this forum.

The below query is dynamically generated from stored procedure based on the parameters passed by.

It works great all other scenarios(based yon your advice we fixed 90% of the scenarios) except the below one (the plan looks very bad for this scenario).

Why does it generates the same plan twice?

     22      NESTED LOOPS
     23       NESTED LOOPS
   1354        TABLE ACCESS BY INDEX ROWID PROFILEDUSER
   1354         AND-EQUAL
   3392          INDEX RANGE SCAN (object id 1132283)
   2657          INDEX RANGE SCAN (object id 1132607)
   1375        TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES
   2706         INDEX UNIQUE SCAN (object id 951554)
     22       TABLE ACCESS BY INDEX ROWID COUNTRIES
     44        INDEX RANGE SCAN (object id 1131957)




SELECT * FROM (
 SELECT  /*+ FIRST_ROWS */ e.userstatusid,
                P.processed_by,
                P.last_name,
                P.first_name,
                P.company_name,
                c.countryname,
                e.customerid,
                TO_CHAR(e.LASTUPDATEDATE,'dd MON, yy ')||'AT '||TO_CHAR(e.LASTUPDATEDATE,'hh24:mi ')||'GMT' formattedlastupd
atedate,
                P.userid,
                business_country_id
 FROM   (SELECT * FROM PROFILEDUSER WHERE hsbc_user_category IN ('GIB','HIBM')) P,
        (SELECT  customerid,userstatusid,profileduserid,lastupdatedate FROM EXTENDEDATTRIBUTES WHERE lastupdatedate >= SYSDA
TE-30) e,
        COUNTRIES  c
 WHERE  P.profileduserid= e.profileduserid
 AND    P.business_country_id = c.countryabbrev
 AND    p.processed_by in ('3')
and     p.region in ('4051')
and e.userstatusid in (1,113)
ORDER BY e.LASTUPDATEDATE desc
)
WHERE ROWNUM <=20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=72 Card=2 Bytes=452)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=72 Card=2 Bytes=452)
   3    2       SORT (ORDER BY STOPKEY) (Cost=72 Card=2 Bytes=284)
   4    3         CONCATENATION
   5    4           NESTED LOOPS (Cost=31 Card=1 Bytes=142)
   6    5             NESTED LOOPS (Cost=29 Card=1 Bytes=128)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'PROFILEDUSER' (Cost=27 Card=1 Bytes=75)
   8    7                 AND-EQUAL
   9    8                   INDEX (RANGE SCAN) OF 'PROFILEDUSER_IX05' (NON-UNIQUE) (Cost=4 Card=1)
  10    8                   INDEX (RANGE SCAN) OF 'PROFILEDUSER_IX06' (NON-UNIQUE) (Cost=4 Card=1) 
  11    6               TABLE ACCESS (BY INDEX ROWID) OF 'EXTENDEDATTRIBUTES' (Cost=2 Card=39 Bytes=2067)
  12   11                 INDEX (UNIQUE SCAN) OF 'ATTRIBUTES_PK' (UNIQUE) (Cost=1 Card=39)
  13    5             TABLE ACCESS (BY INDEX ROWID) OF 'COUNTRIES' (Cost=2 Card=242 Bytes=3388)
  14   13               INDEX (RANGE SCAN) OF 'COUNTRIES_IX01' (NON-UNIQUE) (Cost=1 Card=242)
  15    4           NESTED LOOPS (Cost=31 Card=1 Bytes=142)
  16   15             NESTED LOOPS (Cost=29 Card=1 Bytes=128)
  17   16               TABLE ACCESS (BY INDEX ROWID) OF 'PROFILEDUSER' (Cost=27 Card=1 Bytes=75)
  18   17                 AND-EQUAL
  19   18                   INDEX (RANGE SCAN) OF 'PROFILEDUSER_IX05' (NON-UNIQUE) (Cost=4 Card=1)
  20   18                   INDEX (RANGE SCAN) OF 'PROFILEDUSER_IX06' (NON-UNIQUE) (Cost=4 Card=1)
  21   16               TABLE ACCESS (BY INDEX ROWID) OF 'EXTENDEDATTRIBUTES' (Cost=2 Card=39 Bytes=2067)
  22   21                 INDEX (UNIQUE SCAN) OF 'ATTRIBUTES_PK' (UNIQUE) (Cost=1 Card=39)
  23   15             TABLE ACCESS (BY INDEX ROWID) OF 'COUNTRIES' (Cost=2 Card=242 Bytes=3388)
  24   23               INDEX (RANGE SCAN) OF 'COUNTRIES_IX01' (NON-UNIQUE) (Cost=1 Card=242)

 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      24148  consistent gets
       2647  physical reads
          0  redo size
       3912  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      5.53      16.22       2647      24148          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      5.54      16.23       2647      24148          0          20


Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 180
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     20  COUNT STOPKEY
     20   VIEW
     20    SORT ORDER BY STOPKEY
     22     CONCATENATION
      0      NESTED LOOPS
      1       NESTED LOOPS
   1354        TABLE ACCESS BY INDEX ROWID PROFILEDUSER
   1354         AND-EQUAL
   3392          INDEX RANGE SCAN (object id 1132283)
   2657          INDEX RANGE SCAN (object id 1132607)
   1353        TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES
   2706         INDEX UNIQUE SCAN (object id 951554)
      0       TABLE ACCESS BY INDEX ROWID COUNTRIES
      0        INDEX RANGE SCAN (object id 1131957)
     22      NESTED LOOPS
     23       NESTED LOOPS
   1354        TABLE ACCESS BY INDEX ROWID PROFILEDUSER
   1354         AND-EQUAL
   3392          INDEX RANGE SCAN (object id 1132283)
   2657          INDEX RANGE SCAN (object id 1132607)
   1375        TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES
   2706         INDEX UNIQUE SCAN (object id 951554)
     22       TABLE ACCESS BY INDEX ROWID COUNTRIES
     44        INDEX RANGE SCAN (object id 1131957)
 
********************************************************************************

 OBJECT_ID INDEX_NAME                     COLUMN_NAME
---------- ------------------------------ ------------------------------
    951554 ATTRIBUTES_PK                  PROFILEDUSERID
    951555 EXTENDEDATTRIBUTES_FK01        COMPANYINCCOUNTRY
    951556 EXTENDEDATTRIBUTES_FK02        COMPANYSECTORID
    951562 EXTENDEDATTRIBUTES_FK03        USERSTATUSID
    951563 EXTENDEDATTRIBUTES_FK04        STAFFUSERDEPTID
    951557 EXTENDEDATTRIBUTES_FK06        INVESTORCLASSID
    951558 EXTENDEDATTRIBUTES_FK07        LANGUAGEID
    951559 EXTENDEDATTRIBUTES_FK08        TIMEZONEID
    951560 EXTENDEDATTRIBUTES_FK09        USERTIERID
    951561 EXTENDEDATTRIBUTES_FK10        USERTYPEID
   1131958 EXTENDEDATTRIBUTES_IX01        CUSTOMERID
   1190000 EXTENDEDATTRIBUTES_IX03        LASTUPDATEDATE
   1190000 EXTENDEDATTRIBUTES_IX03        USERSTATUSID
   1005388 SYS_IOT_TOP_1005387            PROFILEDUSERID


SQL> select count(*) from profileduser;
  COUNT(*)
----------
    483980

SQL> select count(*) from extendedattributes;
  COUNT(*)
----------
     18028

SQL> select count(*) from countries;
  COUNT(*)
----------
     239




TABLE_NAME           INDEX_NAME                     COLUMN_NAME          
COLUMN_POSITION
-------------------- ------------------------------ -------------------- 
---------------
PROFILEDUSER         PROFILEDUSER_I01               HSBC_USER_CATEGORY           
      1
PROFILEDUSER         PROFILEDUSER_I02               BUSINESS_COUNTRY_ID          
      1
PROFILEDUSER         PROFILEDUSER_IX01              SEARCH_FIRST_NAME            
      1
PROFILEDUSER         PROFILEDUSER_IX02              SEARCH_LAST_NAME             
      1
PROFILEDUSER         PROFILEDUSER_IX03              SEARCH_COMPANY_NAME          
      1
PROFILEDUSER         PROFILEDUSER_IX04              USERID                       
      1
PROFILEDUSER         PROFILEDUSER_IX04              PROFILEDUSERID               
      2
PROFILEDUSER         PROFILEDUSER_FK10              CAMLEVELID                   
      1
PROFILEDUSER         PROFILEDUSER_PK                PROFILEDUSERID               
      1
PROFILEDUSER         PROFILEDUSER_AK1               USERID                       
      1
PROFILEDUSER         PROFILEDUSER_IX05              REGION                       
      1
PROFILEDUSER         PROFILEDUSER_IX06              PROCESSED_BY                 
      1

Indexes on EXTENDEDATTRIBUTES
==============================
TABLE_NAME           INDEX_NAME                     COLUMN_NAME          
COLUMN_POSITION
-------------------- ------------------------------ -------------------- 
---------------
EXTENDEDATTRIBUTES   ATTRIBUTES_PK                  PROFILEDUSERID               
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK01        COMPANYINCCOUNTRY            
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK02        COMPANYSECTORID              
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK06        INVESTORCLASSID              
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK07        LANGUAGEID                   
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK08        TIMEZONEID                   
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK09        USERTIERID                   
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK03        USERSTATUSID                 
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK04        STAFFUSERDEPTID              
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_FK10        USERTYPEID                   
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_IX01        CUSTOMERID                   
      1
EXTENDEDATTRIBUTES   EXTENDEDATTRIBUTES_IX03        LASTUPDATEDATE               
      1
 

 

Dear Tom,

I understand that non-selective indexes are because of huge percentage of NULL values.

Region(1132283= 'PROFILEDUSER_IX05') and Processed_by (1132607='PROFILEDUSER_IX06') column has 80% NULL values.

Non-selective is avoided only by creating composite indexes(along with NOT NULL column). Is that correct?

Are there any other better way to handle NON_SELECTIVE indexes?

Again, thanks for your time and help.

This thread is really good one to explore, how easy to take backups for big tables in oracle. thanks tom.

<asktom>
a non-selective index is because it is non-selective.
</asktom>
I am just waiting for the time to ask new question about non-selective indexes.

It would be great if you please explain little bit more with an example?

Query Q1 uses Table T1 and Index I1
Query Q2 uses Table T1 and Index I1

Here, index I1 may be non-slective for query Q1 but the same time I1 may be slective for Q2, Is that correct?

Tom,Thank you so much! Thank you so much!!

Tom,

this is exatly what i knew about exchange partition....
"A partition exchange is simply an exchange of segments -- no movement of data
takes place. It is a data dictionary update".

BUT... i have a table A that is partitioned with 10million records in each parition (2 columns & 1 PK constraint + index)...

I exchange one of the partitions (P1) INCLUDING INDEXES WITHOUT VALIDATION... great... timing 00:00:00.

Now, when i want to put the data back into the table it takes about 10 minutes...

what could be the reason? the only differnece between past & today is that I am working ona a RAC.

TIA,
Pravin

If you were going to merge two partitions p1, p2 into p12, and you have already collected the statistics on the partition level p1, p2, after the merge, do you need to collect the statistics on P12? How about vice versa?

Hi..
Is there any danger if I use a partitioned table to store the history of records in that table it self, identified by a version number included in primary key.
Data would look like following
Primary Key would be (EMPNO,Ver)
Where I have a partition for ver=0 and one for > 0
were ver 0 indicates the latest record and 1 the oldest.
and highest ver is the figure before last update.

EMPNO Ver Name Sala
7369 0 SMITH 800
7499 0 ALLEN 1600
7521 0 WARD 1250
7566 0 JONES 2975
7654 0 MARTIN 1250
7698 0 BLAKE 2850
7782 0 CLARK 2450
7788 0 SCOTT 3000
7839 0 KING 5000
7844 0 TURNER 1500
7900 0 JAMES 950
7902 0 FORD 3000
7934 0 MILLER 2510
9988 0 naveen 10000
9987 0 Anto 10000
7369 1 SMITH 400
7499 1 ALLEN 1500
7521 1 WARD 1000
7566 1 JONES 2500
7782 1 CLARK 2000
7499 2 ALLEN 1600
7566 2 JONES 2550
7499 3 ALLEN 1601

What do you think about this, being an expert.?

My main aim was to avoid too much tables, and associated policies and statements to select.
I have FGAC implemented, so to avoid policies on each tables.
Rather say i was doing kind of R&D, and likes to know about any potential dangers

CREATE TABLE TEST
(
EMPNO NUMBER(5),
VERSION NUMBER(5),
ENAME VARCHAR2(25 BYTE),
SAL NUMBER(5)
)
TABLESPACE USERS
PARTITION BY RANGE (VERSION)
(
PARTITION ORIG VALUES LESS THAN (1),
PARTITION HISTO VALUES LESS THAN (MAXVALUE)
);
--####################################################

ALTER TABLE TEST ADD (
CONSTRAINT PK_TEST PRIMARY KEY (EMPNO, VERSION)
USING INDEX
TABLESPACE USERS
);
--####################################################

CREATE OR REPLACE TRIGGER TEST_TRIG
BEFORE UPDATE
ON TEST REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into scott.test values (:old.empno,(select max(version)+1 from scott.test where empno=:old.empno),:old.ename,:old.sal);
commit;
END;
--###################################################

When I use something like this I don't need to create a table for all history data.
I will be storing them inside the same table with in a different partitions.
My single FGAC policy will work and I don't need
to create additional FGAC policy for the history table.
and as a bonus I get all the benefits of Partitions.

I was looking for any potential danger in this design.

Hi..
'is always "1"' -- its 0
0 indicates the current version.
(ver 0 indicates the latest/current record
and 1 the oldest.
and highest ver is the figure before last update.)

SCOTT@isro-02-11:07:27:35>insert into test values ( 1, 0, 'x', 100 );

1 row created.

SCOTT@isro-02-11:07:27:35>commit;

Commit complete.
----------------------------------------------------

Session 1
SCOTT@isro-02-11:07:28:54>update test set sal = 101 where empno = 1 and version = 0;

1 row updated.
-----------------------------------------

Session 2
SCOTT@isro-02-11:07:29:36>update test set sal = 102 where empno = 1 and version = 0;

1 row updated. -- this comes after session 1 commit;
------------------------------------

Session 1
SCOTT@isro-02-11:07:28:54>commit;

Commit complete.
----------------------

Session 2
SCOTT@isro-02-11:07:29:36>commit;

Commit complete.


SCOTT@isro-02-11:07:29:36>select * from test where empno=1;

EMPNO VERSION ENAME SAL
---------- ---------- ------------------------- ----------
1 0 x 102
1 1 x 100
1 2 x 100
1 3 x 101

4 rows selected.

I see here 4 rows, and it should have been 3 only
1. original version 0
2. first update version 1
3. second update version 2

like this.

1 0 x 102
1 1 x 100
1 2 x 101

Now i am confused .. I feel the trigger fires two times
Why it fires two times. ? and is there any way i can solve this.

you cannot in "real life" use autonomous transactions in the manner you are.  It is just a really utterly "wrong thing to do"

there is nothing to fix here -- except "erase the code that exists".  read 

to see "why", but there is NO FIX with that evil autonomous transaction (just the "update two times in the same session" example should be sufficient for that!)

Perhaps what can work for you is:

ops$tkyte@ORA9IR2> CREATE TABLE TEST
  2  (
  3    EMPNO    NUMBER(5),
  4    VERSION  NUMBER(5),
  5    ENAME    VARCHAR2(25 BYTE),
  6    SAL      NUMBER(5)
  7  );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view test_v
  2  as
  3  select * from test where version = 0;
 
View created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into test_v values ( 1, 0, 'x', 101 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into test_v values ( 2, 0, 'x', 102 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into test_v values ( 3, 0, 'x', 103 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop sequence version_seq;
 
Sequence dropped.
 
ops$tkyte@ORA9IR2> create sequence version_seq;
 
Sequence created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger test_v_update
  2  instead of update on test_v
  3  begin
  4          update test set version = version_seq.nextval
  5       where empno = :old.empno and version = 0;
  6      insert into test values
  7      ( :new.empno, 0, :new.ename, :new.sal );
  8  end;
  9  /
 
Trigger created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> select * from test;
 
     EMPNO    VERSION ENAME                            SAL
---------- ---------- ------------------------- ----------
         1          1 x                                101
         2          0 x                                102
         3          0 x                                103
         1          2 x                                111
         1          3 x                                122
         1          4 x                                134
         1          5 x                                147
         1          0 x                                162
 
8 rows selected.
 
ops$tkyte@ORA9IR2> select * from test_v;
 
     EMPNO    VERSION ENAME                            SAL
---------- ---------- ------------------------- ----------
         2          0 x                                102
         3          0 x                                103
         1          0 x                                162
 
 

I think
The tigger should be
CREATE OR REPLACE TRIGGER SCOTT.TEST_TRIG
AFTER UPDATE
ON SCOTT.TEST
REFERENCING NEW AS NEW OLD AS OLD
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into scott.test values (:old.empno,(select max(version)+1 from scott.test where empno=:old.empno),:old.ename,:old.sal);
commit;
END;

I got

EMPNO VERSION ENAME SAL
---------- ---------- ------------------------- ----------
1 0 x 102
1 1 x 100
1 2 x 101

am i correct now?

>
> b) make it so we can get the data from BIG_TABLE to
> another database (why why why another database?
> would make much greater sense to use a single database
> but anyway)
> ...

Well, it is quite common practic (is it?):
if you have havy loaded OLTP database, which grows in size faster than lineary,
then, typically, you need associated archive database to store historycal data.

So, it helps keep speed OLTP database growing in size about the speed GB per $ grows (lineary? correct me).

Reasons to operate this way:

1.) historical data can be managed on cheaper hardware (RAID5 comparing to RAID1, stand-alone box comparing to cluster)

2.) OLTP performance is better because of smaller indexes, better caching

3.) AD-HOC queries, reporting users are bounded to archive database (isolated this way) and are not harming critical OLTP system performance

4.) Databases (and instances) can be each tuned better (for OLTP xor DSS workload)

5.) Disaster, media failure recovery times are potentially smaller for mission-critical OLTP.

6.) Some saving in Oracle licenses? ...

Well, disadvantages:
1.) additional automated data flow need maintenance
2.) additional database to manage

Which data are historical?
Depends on your company buisiness.
Good examples: banking transactions which are older than 1 year; logs, which are older than 1 mounths, CDR (telecom: call detail records), old bills etc..
Historical data cannot be just deleted - it is still important information (for enquiry, bookkeeping), but availability and accessibility requirements for it are just lower.
If your huge archive database is unavailable for a while, main business operations still can proceed in OLTP system.

By the way, Oracle has no complete mechanism for binding such kind of "time windows".

Offcause, Oracle start are using another approach - "single source of thrue"(?) - (one database for all data), for example, in Applications.

Tom, please, bit this arguments, provide reasons for "one database".

Sorry for my English,
Sincerely,

1.) Yes, i'm agree. SAME should not be interpreted literally
But there is possibility even to use mix of different hardware platforms (and vendors), in distributed database environment.
Awesome benefit.

2.) Yes.

3.) Yes, grate.
I really have to study this feature (Resource Manager).

4.) Yes.

5.) Yes, i'm agree.
Only one remark: to recover "OLTP part" from media failure archived logs generated by "DSS&DWH&OLAP part" must be restored and processed. (offcouse, nologging can be used)

6.) RAC seems far too expensive to achieve goals 1-5, but idea is worths

Anyway,
Thank You very much,
Great explanation.

Hi.
Your method did worked but it wont if i use the partitions.
an i used autonomous transactions because i wanted capture the update attempts also. it doesn't matters user commited or not, rather it matters to me if he attemted or not. I am capturing the user details also along with this.

But really i didn't knew the danger of autonomous transactions.

Thanks for your quick reply.

why not?
ops$tkyte@ORA9IR2> CREATE TABLE TEST
  2  (
  3    EMPNO    NUMBER(5),
  4    VERSION  NUMBER(5),
  5    ENAME    VARCHAR2(25 BYTE),
  6    SAL      NUMBER(5)
  7  ) <b>enable row movement</b>
  8  PARTITION BY RANGE (VERSION)
  9  (
 10    PARTITION ORIG VALUES LESS THAN (1),
 11    PARTITION HISTO VALUES LESS THAN (MAXVALUE)
 12  );
 
Table created.

.....

ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update test_v set sal = sal*1.1 where empno = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> select * from test;
 
     EMPNO    VERSION ENAME                            SAL
---------- ---------- ------------------------- ----------
         2          0 x                                102
         3          0 x                                103
         1          0 x                                162
         1          1 x                                101
         1          2 x                                111
         1          3 x                                122
         1          4 x                                134
         1          5 x                                147
 
8 rows selected.
 
ops$tkyte@ORA9IR2> select * from test_v;
 
     EMPNO    VERSION ENAME                            SAL
---------- ---------- ------------------------- ----------
         2          0 x                                102
         3          0 x                                103
         1          0 x                                162




if you use an autonomous transaction <b>you have done it provably wrong wrong wrong wrong wrong</b>.  Period.  there is no disagreeing with this statement. 

Hi..
I agree with you. You are a genius.

I now have a clear idea of these things.
I have done it the way you said.
--rename test to test1;
--create view test as select * from test1;
and then your trigger.

plus this way.( where there is no row movement)
CREATE OR REPLACE TRIGGER SCOTT.TRIG_VIEW_TEST
INSTEAD OF UPDATE
ON SCOTT.TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
update scott.test1 set empno = :new.empno, ename=:new.ename,sal=:new.sal where empno=:old.empno and version=0;
insert into scott.test1 values (:old.empno,(select max(version)+1 from scott.test1 where empno=:old.empno),:old.ename,:old.sal);
END;

thanks very much.
One more question
Do you have any plans in future to visit India for some session or speeches. ?

Yes I am using that sequence now.
Thanks

Can I rename a partition ?

Thanks

sql reference would be the first place to look.....

  1  CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12* )
 13  /
 
Table created.
 
ops$tkyte@ORA10GR1> alter table t rename partition part1 to hello_world;
 
Table altered.
 
 

We have a large partitioned table, +1 GRows, with 4 global indexes. The table is partitioned on batch load id, i.e. all data we load each day +2 MRows is in the same partition. Normally we insert with /*+ append */ and it took abount 1h including some other processing. Suddenly (there was some OS changes) we hit an Oracle bug and we could not use /*+ append */ and the load took about 10h. While moving the data to a different tablespace, with other block size, we had to remove the indexes and the load time dropped down to 1h again. With the index back in place we are back with 10h loading but we also installed the Oracle support suggested patch and hopefully we can switch back to /*+ append */.

My question is how/why is the impact of the indexes so large, a factor 10, when we don't use /*+ append */? Does a global index a larger impact than a local or an ordinary index on a non-partitioned table?

Is there any way to know that in the last partition the last value is going to run out? For eg., if I have a table tab1 with 3 partitions on some sequential id..

partition 1 - seq id between 1 and 100
partition 2 - seq id between 101 and 200
partition 3 - seq id between 201 and 300.

Is there any way that I can know when the max(id) in tab1 is nearing 300 so that I can create the next partition?

Thanks,
Vidya.

<quote>Is there any way that I can know when the max(id) in tab1 is nearing 300 so that I can create the next partition?</quote>

The approach with max(id) seems too "reactive" and prone to error.

I would take a more "proactive" approach ... have the MAXVALUE partition and probe it for content ... split the MAXVALUE partition when it starts getting rows ... hence no inserts will fail because of ORA-14400.

Hi Tom,

What I wanted was somehow to find out the max value of the current partition and if

max value of current partition - max(id) = 1000 (or some number) then I have to be notified.

Thanks.

Hi Gabe,

Thanks for your input to the solution. But I didn't understand what you meant when you said that I have to split the partition when it starts getting rows. Can you show me with an example?

Thanks.

create table x (n number(9) not null, v varchar2(10))
partition by range(n)
( partition part_1 values less than ( 101 )
,partition part_2 values less than ( 201 )
);

If in between probing for max(n) and reacting to it (adding the new partition) one does:

flip@FLOP> insert into x values (201,null);
insert into x values (201,null)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

then there is a window of “opportunity” for your application to fail …a range-partitioned table without a MAXVALUE partition implicitly imposes a CHECK-like constraint on the table.

If …

flip@FLOP> drop table x;

Table dropped.

flip@FLOP> create table x (n number(9) not null, v varchar2(10))
2 partition by range(n)
3 (
4 partition part_1 values less than ( 101 ),
5 partition part_2 values less than ( 201 ),
6 partition part_max values less than ( maxvalue )
7 );

Table created.

flip@FLOP> insert into x values (201,null);

1 row created.

then we avoid the possibility of application failure and the probe for “should I have another partition?” becomes:

flip@FLOP> select count(*) from x partition (part_max) where rownum < 2;

COUNT(*)
----------
1

and the partition split:

flip@FLOP> alter table x
2 split partition part_max at (301) into
3 ( partition part_3
4 ,partition part_max
5 );

Table altered.

flip@FLOP> exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );

PL/SQL procedure successfully completed.

flip@FLOP> exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

flip@FLOP> select dbms_metadata.get_ddl('TABLE','X') from dual;

DBMS_METADATA.GET_DDL('TABLE','X')
--------------------------------------------------------------------------------

CREATE TABLE "FLIP"."X"
( "N" NUMBER(9,0) NOT NULL ENABLE,
"V" VARCHAR2(10)
)
PARTITION BY RANGE ("N")
(PARTITION "PART_1" VALUES LESS THAN (101) NOCOMPRESS ,
PARTITION "PART_2" VALUES LESS THAN (201) NOCOMPRESS ,
PARTITION "PART_3" VALUES LESS THAN (301) NOCOMPRESS ,
PARTITION "PART_MAX" VALUES LESS THAN (MAXVALUE) NOCOMPRESS )

Thanks for being here Tom over the years.
I have your books and read them many times each. I cannot count or relate you how many times you have helped me discover a great solution. I often take what you write and expand it. Your turning strategies have been most influential for me to become a very very good application tuner. I thank you for your professionalism and excellent writing/speaking manner.
Regards,
J
a fan and thankful ORACLE DBA/DEVELOPER

Hello Tom,
I renamed some table partitions but the index partitions
for those renamed table partitions still point to old
table partitions.Doesn't index maintenance automatic when
we rename table partitions?

Hi Tom,
I have the following table
CLIENT_DETAIL
----------------
CLIENTNO NOT NULL NUMBER(9)
QUARTER NOT NULL NUMBER(5)
MONTH NOT NULL NUMBER(2)
PARTITION_CD NOT NULL NUMBER
CATEGORY VARCHAR2(2)
TYPE_PROG VARCHAR2(2)

Where first four fields is my Primary key. Now If I have a List partition on Partition_cd would the PK Index be treated as Global Index?
I dont know whether my question is correct or not but do I need to rebuild my PK everytime I truncate or drop a partition?

Thanks a Lot
Sudip

CREATE TABLE ERS_ACF_AUDIT
(
GUID VARCHAR2(50)
, SUB_GUID VARCHAR2(50)
, SERVICE VARCHAR2(22)
, SERVICE_VSN VARCHAR2(22)
, BUS_MSG_TYPE VARCHAR2(100)
, TIME_STAMP TIMESTAMP(6)
, DATE_LOADED DATE

)
PARTITION BY RANGE(TIME_STAMP)
(PARTITION RA_ACF_MAXVALUE VALUES LESS THAN (MAXVALUE));

We need to create subpartition by LIST on SERVICE column.

Table has already been created and it is having the large volume of Data. Is it possible to create the subpartiton by LIST by altering the table?

If not then what is the method to achieve the same.

Hi Tom,

We have a table t1 (c1,c2,c3)
It is range portioned on c1 and then list sub portioned on c2. 4 partitions and 7 sub partitions . So in total of 28 partitions

Now we have a query which uses c3 in where clause . c3 also has local index .

Below is the snip of the plan.

| 13 | PARTITION RANGE ALL | | 1 | 42 | 852 (0)| 00:00:12 | 1 | 17 |
| 14 | PARTITION LIST ALL | | 1 | 42 | 852 (0)| 00:00:12 | 1 | LAST |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID| t | 1 | 42 | 852 (0)| 00:00:12 | 1 | 441 |
|* 16 | INDEX RANGE SCAN | t_idx | 76 | | 850 (0)| 00:00:12 | 1 | 441 |

How can we have partition pruning in this scenario/ Any other partitioning scheme which would be useful.

Right now, because the index on c3 is local, every of the
28 subpartitions has its own version of that index,
consisting of the c3 data in the corresponding
subpartition. Therefore, without also referencing c1 and c2
in your query, all of these 28 indexes must be scanned, so
partition pruning is not possible. This is also what your
plan reflects.

You could change the index on c3 from a local to a global
index, which would give you 1 big index on c3 instead of 28
small indexes (at least if you don't partition that index
using another partitioning scheme). Beware of the
consequences, though. For example, dropping a subpartition
will require index maintenance if the index is global; for
a local index the index for the subpartition is simply
dropped together with the subpartition.

Hello Sir,
We want to merge the data of 8 schemas having same data model into one single schema. To avoid performance issues, we are planning to go for partitioning with application identifier(To identify, from which schema the entry is coming from) for all the tables. After merging , the data in tables would increase to 8 times of original storage(approx).
Application1 Schema1
Application2 Schema2
Application3 Schema3
Application4 Schema4
Application5 Schema5
Application6 Schema6
Application7 Schema7
Application8 Schema8
All the application users would access one application. ie Applicatoin_New
All the schemas would merge into one schema.ie Schema_New

All the users connecting to the above applications would connect to Application_New and work on Schema_New.

My question is ,Will there be any improvement in the performance? Or at least the same performance as earlier?

Thanks in advance ...
Sree

Thank you Tom....

One more clarification...
If we have different schemas with same data model for different applications....
In my case, Now we have applications for different departments in different schemas with same data model.....
We are planning to merge the schemas with department as application identifier.....

Which is the better way... having one schema for all the departments or one schema for one department?

Thanks again....