Why would you want to index CREATED_TIME
if you're already partitioning on CREATED_TIME
? If you are regularly querying the data looking for date ranges much smaller than your partition grain, that is, you're regularly querying for date ranges of a couple hours, you probably want to adjust your partitioning strategy to create partitions more frequently. If you query recent data in smaller intervals than old data-- for example, you regularly aggregate by hour over the past day, aggregate by day over the past week, and aggregate by month over the past year-- you may want to merge smaller partitions together as they age (potentially in addition to compressing older partitions).
Additionally, are you sure that you would want to index CREATED_TIME
rather than creating a materialized view that pre-aggregates the data by some smaller interval than the partition? If you're aggregating data in various queries by hour, day, and month, for example, you'd generally be better served by creating a materialized view that pre-aggregated the data at the smallest grain (hour) and an Oracle dimension object that allowed query rewrite to use the materialized view to aggregate the hourly rows into daily or monthly results rather than trying to read all the data from the base table.
A trigger on schema
(with no schema specified) doesn't fire for all schemas. It only fires when the triggering action is run by the user who owns that trigger.
So they are quite different, and are not interchangeable.
Here's an example that sets up a logging table, and three create
triggers: on database
, and on schema
for users foo
and bar
:
Connected. -- as mat
SQL> create table mat.log (dt timestamp, who varchar(3),
2 cur varchar(10), own varchar(42), obj varchar(42));
Table created.
SQL> create or replace trigger db_trig
2 after create on database
3 begin
4 insert into mat.log values (systimestamp, 'db', user,
5 ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
6 end;
7 /
Trigger created.
SQL> connect foo/foo
Connected.
SQL> create or replace trigger foo_trig
2 after create on schema
3 begin
4 insert into mat.log values (systimestamp, 'foo', user,
5 ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
6 end;
7 /
Trigger created.
SQL> connect bar/bar
Connected.
SQL> create or replace trigger bar_trig
2 after create on schema
3 begin
4 insert into mat.log values (systimestamp, 'bar', user,
5 ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
6 end;
7 /
Trigger created.
Now let's create a table in foo
's schema, as foo
:
SQL> connect foo/foo
Connected.
SQL> create table foo.foo_stuff (id number);
Table created.
And let's create a table in foo
's schema, as bar
:
SQL> connect bar/bar
Connected.
SQL> create table foo.bar_stuff (id number);
Table created.
Here's what we've logged:
SQL> select * from mat.log order by dt;
DT WHO CUR OWN OBJ
------------------------------ --- ---------- ---------- ---------------
25-NOV-12 07.52.03.797794 PM db FOO FOO FOO_TRIG
25-NOV-12 07.52.03.828670 PM db BAR BAR BAR_TRIG
25-NOV-12 07.52.03.865334 PM foo FOO FOO FOO_STUFF
25-NOV-12 07.52.03.865579 PM db FOO FOO FOO_STUFF
25-NOV-12 07.52.03.894672 PM bar BAR FOO BAR_STUFF
25-NOV-12 07.52.03.894911 PM db BAR FOO BAR_STUFF
6 rows selected.
So:
- the two
create trigger
statements were logged by the "global" after create on database
trigger. That trigger also logged everything else.
foo
's after create on schema
logged the table creation that was done by foo
bar
's trigger logged the table creation that was run by bar
himself, even though bar
created a table in foo
's schema.
Best Answer
Sean, I believe that the local text index is part of the Oracle Text feature of Oracle that is specific to working with documents, such as Web sites, digital libraries, document or warehouses. While the Domain Indexes are indexes that we use in day-to-day.
The following documentation will be helpful: About Oracle Text: http://docs.oracle.com/cd/B28359_01/text.111/b28303/overview.htm (In the third chapter there is information about the indexes)
About the Domain Indexes: http://docs.oracle.com/cd/B10500_01/appdev.920/a96595/dci07idx.htm