-
Gee, the missundertanding of the NOLOGGING option is simply amaising! It keeps poping up all the time in this forum (not only in this thread), showing how people simply don't understand what the NOLOGGING realy does (and what it doesn't).
Seems also that Oracle has been very unfortunate in naming this option. First it was named as "UNRECOVERABLE", but I guess this sounded too scary. So they changed it to NOLOGGING, but now people obviously conclude from this name that DML operations on such a table don't get logged at all. This is so far from the truth!
Setting the NOLOGGING/LOGGING for a table only affects three type of activities:
- Insert phase of the CTAS (Create Table As Select)
- Direct load path of SQL*Loader (DIRECT=Y)
- Direct path INSERTS (with hint /*+ APPEND*/)
It can *in no way* affect the deletes or updates of any kind, it also doesn't affect normal inserts. So if you are altering a table to NOLOGGING just to speed the deletes you are waisting your time.
-
First, I agree that something needs to change. Partitioning sounds like the perfect solution! If not, then I agree with tamilselvan's solution, but I have a different implementation choice for you.
First of all, going with the flag solution raises some issues. First of all, if this is an OLTP database, then you will probably need to add the new field to the beginning of every index on the table, because it *will* be in every query. If this is a DSS or OLAP database, then we are in better shape, as a single bitmap index will suffice.
Now, on to the implementation. Instead of modifying every query in the app, it would be easier to implement some fine-grained access control. Now, I haven't actually implemented any of these yet, so bear with me if I don't get it perfect. But it seems that all you have to do is:
- Make a function that returns a WHERE predicate:
CREATE OR REPLACE FUNCTION IgnoreDeleteFlag
(
---P_SCHEMA IN VARCHAR2,
---P_OBJECT IN VARCHAR2
)
RETURN
---VARCHAR2
AS
BEGIN
---RETURN---'Deleted_Flag = ''0''';
END;
- Then, create a policy that simply links that function to the table:
DBMS_RLS.ADD_POLICY
---(
------OBJECT_SCHEMA => 'MY_SCHEMA',
------OBJECT_NAME => 'BIG_TABLE',
------POLICY_NAME => 'WHATEVER',
------FUNCTION_SCHEMA => 'MY_SCHEMA',
------POLICY_FUNCTION => 'IgnoreDeleteFlag',
------STATEMENT_TYPES => 'SELECT, UPDATE' ,
------UPDATE_CHECK => TRUE
---);
Now, every single SELECT against that table will automatically have AND Deleted_Flg = '0' appended to it.
The drawbacks are that this 'addition' to the statement is made at the back-end and *completely* hidden from the outside world, including the SGA, explain plans, etc. This is why it is important to understand the implications beforehand, as you can't double-check them after implementation. Again, however, if this is a DSS database, the risk would be mitigated greatly by the bitmap index.
The advantage is that it is easy as pie to implement.
HTH,
- Chris
BTW - as mentioned before, don't forget to rebuild your indexes after the delete.
[Edited by chrisrlong on 04-05-2001 at 03:51 PM]
-
In response to JModic's observation. First, as always, he is absolutely correct. However, I would add another entry to his list of non-logged operations:
Parallel inserts, but they require:
- ALTER SESSION ENABLE/FORCE PARALLEL DML
- table PARALLEL attribute or statement PARALLEL hint (an APPEND hint is optional)
...to become Direct-Load inserts
- Chris
-
What I think u could do is write a procedure which will select
all the rows from the table and insert into a temp table with the insert into * select * from a excluding the rows you delete statement,then truncate the table and reload using the temp table. Hope this helps.
-
First of all, I'd like to thank you all Guys for sharing your thoughts. Right now I am thinking that the best way to do this is by partitioning (31 partition for 31 days). This application will be 24X7 operation, so Sunday will not be a low activity day, My question now is can we really truncate table by partition, I haven’t done this before. Another thing is can we create a partition in this manner.
create table message( acct_no number(5) unique,
sales_amount number(8) not null,
acct_date date not null)
partition by range(acct_date)
(partition Day1 values to_char(‘acct_date,’DD’)=(1) tablespace day1,
partition Day2 values to_char(‘acct_date,’DD’)=(2) tablespace day2,
partition Day3 values to_char(‘acct_date,’DD’)=(3) tablespace day3,
partition Day4 values to_char(‘acct_date,’DD’)=(4) tablespace day4,
.
.
.
partition Day31 values to_char(‘acct_date,’DD’)=(31) tablespace day34,
);
Keep the ideas flowing……
Thanx,
Ed
-
I believe you can not give function like to_char(‘acct_date,’DD’) in the partition caluse.
What possibly you could do is, create another column (say Day_Col number(2)) and put the day part into this column whenever rows are added to this table (May be by use of trigger).
Truncating the partition may be cumborsome, when you have global unpartitioned indexes, foreign keys etc.
Just a thought. Some others may have some elegent solutions.
-
we have the same issue in our 24x7 system and ended up into those solutions:
In most cases we can have runtime tables that are small tables used by application to perform buiseness and after transaction is done, records are closed and moved(deleted and inserted) to main table.
main table is a partitioned table with partitions ending with _YYYYMMDD string (eg. TABL1_20010506 for 06-05-2001 partition).
Partitiones are created and truncated by small utility package, that has procedures to add/truncate partitiones of equal datetime intervals (given as days, weeks or quaters)
If the buisenes can be arranged such that records are processed and closed(i.e. moved to main tables) and reports can run on closed records(i.e. main tables)only then for 24x7 system it is quite a good design as maintaning small tables takes seconds or less than to maintain large tables containg milions and more of recors.
Some issues regarding partitioned table:
as was pointed out, global indexes gets unusable.
performance of local index should be lower over global index(if partition prunning can not be used by a query).
if partitioned table has a snapshot then there has to be used some tricks to truncate partition and preserve snapshot log.
and of course Oracle is still debuging partitioned tables from some really nasty bugs, so it depends what version you use.
