Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to calculate age in SQL?

365045Dec 7 2002 — edited Feb 27 2013
I have a table with DATE as one of the column.
How do I calculate age?

Thanks in advance
-Shilpa

Comments

153119
Generally speaking you should not rebuild.
There has been a recent thread discussing this ad nauseam.
Please always search the forum before asking questions like this again!

--
Sybrand Bakker
Senior Oracle DBA
591663
Make use of statspack or AWR.

Also check the status of te indexes of your database using following query.

SQL> SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS FROM DBA_INDEXES WHERE STATUS='INVALID';
108476
Hi,
How can I tell if my Db indexes require rebuilding
First, see the Oracle segment advisor which recommends indexes for rebuilding:

http://www.rampant-books.com/art_floss_segments_shrink.htm

---------------------------------------

See Oracle MetaLink note 122008.1 for the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

---------------------------------------

Oracle's index rebuilding guidelines appear in Metalink note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:

“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”

The note also says that the index rebuilding criteria has changed since the advent of Oracle9i, where a blevel > 4 was a good threshold. It also notes that the pct_deleted column in index_stats from “alter index xxx validate structure” provides a guideline for index rebuilding candidates:

“Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. . .

The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled.

The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding.”

---------------------------------------

Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some tangible benefits to using a larger blocksize:

- Using bigger blocks means more data transfer per I/O call; this is an advantage since the cost of I/O setup dominates the cost of an I/O. . .

- Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

- Using a block size that is k times bigger than your current one will save you (k-1)f/(kb-f) bytes of space for large segments, where f is the size of a block's fixed block header (61 bytes for tables, 57+4n for n-table clusters, 113 for indexes). For example, you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.

- When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

http://www.dba-oracle.com/t_index_rebuilding_issues.htm


Hope this helps. . .

Don Burleson
Oracle Press author
antti.koskinen
Rebuild the index when these conditions are true:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
And as for any other rule of thumb, there are exceptions. A large enough index might not fit into 5 levels, rebuilding an index after a mass delete might not be sensible if there's another big delete later in the batch, if the index is only range scanned etc..

edit: removed "quote" tags

Message was edited by:
antti.koskinen

Message was edited by:
antti.koskinen
108476
Hi Antti,
And as for any other rule of thumb, there are exceptions.
Yes, good observation.

In my experience, it also depends on how the index is used. Indexes that are used for a row ID fetch will never see a performanceimprovement from an index rebuild.
94799
Indexes that are used for a row ID fetch
Do you mean single row fetch? The term 'row ID fetch' seems rather ambiguous to me.
601585
Do you really know what you mean?
For instance ...
When an index is skewed
What on earth do you mean by "skewed"?
Does this mean that there are many free leaf blocks, or leaf nodes are splitted quite often by right-handed insertion?
"skewed" is such an inappropriate term for index.
Quite confusing...
parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance
What on earth is this related to index rebuild?
"Parts of index are accessed more frequently than others"? Does this mean your index is right-handed? If then, what on earth does right-handed index have to do with "index rebuild"?

Please don't just spread what you read in the metalink.
Didn't you ever think that the note you refered has a chance to contain some flaws?
You're a decent consultant(or engineer?), then you must verify or at least
have question before you distribute.

PS) In addition, what on earth does index block size have to do with "index rebuild"?
Your whole excerpts and suggestions are confusing to me, and probably to others.

Typo...
Message was edited by:
Dion_Cho
108476
Hi James,
single row fetch?
Yes, that's much better. . . . Thanks!
108476
Hi Dion
Please don't just spread what you read in the metalink.
MetaLink and the docs are the "official" word from Oracle, right? When I quote my notes, people chastise me for NOT citing the docs! I can't win!
What on earth do you mean by "skewed"?
That wasn't me, I just quoted the docs . . . .

To me, the "skew" is misunderstood, as is "unbalanced", and the question about whether Oracle indexes are self-balancing is largely a matter of semantics. As rows are added to an empty index, Oracle controls the addition of same-level blocks (called “splitting”) until the higher-level index node is unable to hold any more key-pointer pairs. When the index can no longer split (because the owner block is full), Oracle will spawn a whole new index level, keeping the index tree in perfect logical and physical balance.

However, deletes are a different story. Physically, Oracle indexes are always balanced because empty blocks stay inside the tree structure after a massive delete. Logically, Oracle indexes are not self-balancing because Oracle does not remove the dead blocks as they become empty.

This type of “sparse” index is typical of an index on highly-active tables with large-scale inserts, deletes and updates. We may have thousands of empty or near-empty index blocks, and several Oracle execution plans will run longer on this type of “sparse” index

Didn't you ever think that the note you refered has a chance to contain some flaws?
Oh sure. I have my own approach, but I wanted to be "politically correct", and cite the official docs. Here are my personal notes:

http://www.dba-oracle.com/t_index_rebuilding_issues.htm
what on earth does index block size have to do with "index rebuild"?
Excellent question. The benefits of large blocksizes are demonstrated on this OTN thread where we see a demo showing 3x faster performance using a larger block size:

568662

Also, Robin Schumacher has proved that indexes build differently in different blocksizes:

http://www.rampant-books.com/book_2003_1_perf.htm

“As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache.

Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database.“

Hope this helps. . .

Don Burleson
Oracle Press author
601585
Don, please anwer my question~
I suddenly realized that you're totally ignoring my questions.
That's because my English is not natural and fluent?
I'm trying really hard to write good English. :)

Please~~~
You're such a famous person in my region just like other famous engineers including Jonathan, Tom, Tanel and Julian... (but not exactly the same way)

So your technical anwser will be honor~

Oops. you answered my question. Sorry. :)
Message was edited by:
Dion_Cho
153119
Don,

Could you please stop posting the Robin Schumacher drivel?

As was to pointed out to you not so long ago, his approach was fundamentally flawed.

--
Sybrand Bakker
Senior Oracle DBA
108476
Hi Dion,
I suddenly realized that you're totally ignoring my questions.
Sorry, I tried to do them. Please re-state them, and I'll try again!
That's because my English is not natural and fluent?
Hey, it's way better than my Korean! English is a very confusing language.

Check this out, very funny:

http://www.lifeaftercoffee.com/2006/04/07/why-learn-english/
108476
Could you please stop posting the Robin Schumacher drivel?
Oh brother.

First you whine because I don't show reproduceable test cases, and then when I provide reproducable test cases you call them "drivel"!

Make up your mind!

This is a fully reproduceable test case.

What, you don't like "proofs" now? For shame! I'm gonna tell on you . . . .
153119
There is one difference.
Generally speaking Jonathan Lewis, Tom Kyte and Tanel Poder build reproducible test cases.
Don Burleson takes one example and comes up with generic recommendations, based on one isolated case.
Right now he is posting his disastrous advice again, while a few weeks it was pointed out to him his generic 'one size fits all' or 'never touch the application, always throw hardware at the problem' approach doesn't work.
Don Burleson is famous because he publishes so many, usually badly researched, books and uses this forum as his private marketing machine.
Please don't mistake exposure for quality.

--
Sybrand Bakker
Senior Oracle DBA
153119
You really don't understand what you are posting aren't you?
The Schumacher drivel was based upon a select using index_ffs.

Index_ffs are pretty rare.
Both myself and Jonathan Lewis pointed out to you Schumachers approach is flawed. Even you finally admitted that.

You post the Schumacher example because it is printed in a book published by Rampant Press, your publishing company.
Yet another example of the Burleson Marketing Machine.

--
Sybrand Bakker
Senior Oracle DBA
601585
Yes, understood what you're trying to say.

But i don't understand why you're recommeding index rebuild so many times in so many place. If you understand what you're saying, you should admit that "massive delete" is the only reason when index rebuild is helpful.
In my opinion, under normal situation, we have very rare chance to have massive delete, don't we? 1 out of 10,000 or something? Even in that case, "coalesce" is just a better option than index rebuild.
If so, why index rebuild is so important to you?

Shouldn't you say... "Index rebuild is helpful only under *very very* restrictive situation where your whole data is being deleted. So just be very careful..."

PS)
In addition, just because you're a decent consultant, you should be very careful when delivering what other say. Metalink doc is not an exception. If metalink doc has flawes, it your(or our) obligation to detect it and request Oracle to correct it. Don't you think so?
"It's written in metalink... So i have no fault" This attitude is only reasonable for beginners not for professionals like you.
108476
takes one example and comes up with generic recommendations, based on one isolated case.
Ah, but according to your mentors, only one "proof" case is necessary to infer how something works . . .
Please don't mistake exposure for quality.
And please don't insinuate that you are a competant DBA.

But don't take my word for it, let's see what Jonathan says about you:

*****************************************

http://groups.google.com/group/comp.databases.oracle.server/msg/a76710056af45fbe?dmode=source

"Sybrand, Your manners and your lack of care leave much to be desired . . .

You quoted the first line, and commented "utter rubbish" - presumably having failed to bother to go on to the later lines of text. . .

You side-stepped the point, changed the question, and redefined the term DDL to mean 'the components', pointed out that you can save the DDL in a script, and said you didn't see the problem.

"I am allowed to give you the wrong answer because I don't see the problem and I'm not paying attention" is NOT a valid comment. . .

Next time you decide to be offensive, please wait 24 hours
before hitting the <Send> key.
108476
Hi Dion,
under normal situation, we have very rare chance to have massive delete, don't we?
It depends on the system!

For example, consider the popular Clintrial software, a LIMS system that uses Oracle. In Clintrial, experimental results are initially stored as small rows. Later, as more data is collected, the VARCHAR columns expand, causing massive row chaining, and fragmenting the daylights out of secondary indexes, as key values are changed.
"It's written in metalink... So i have no fault"
Sure, Metalink has errors, but it's still the "official" rules for what is allowed and what is not allowed. I've seen shops get de-supported for not following the advice on MetaLink. (e.g. advice not to use some 3rd party tools, etc.).
Jonathan Lewis
>
But don't take my word for it, let's see what
Jonathan says about you:

*****************************************

http://groups.google.com/group/comp.databases.oracle.s
erver/msg/a76710056af45fbe?dmode=source

"Sybrand, Your manners and your lack of care leave
much to be desired . . .

You quoted the first line, and commented "utter
rubbish" - presumably having failed to bother to go
on to the later lines of text. . .

You side-stepped the point, changed the question, and
redefined the term DDL to mean 'the components',
pointed out that you can save the DDL in a script,
and said you didn't see the problem.

"I am allowed to give you the wrong answer because I
don't see the problem and I'm not paying attention"
is NOT a valid comment. . .

Next time you decide to be offensive, please wait 24
hours
before hitting the <Send> key.
Don,

I'm delighted to see that you approve of the way I criticise people for inaccurate or inappropriate responses.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
153119
Don,

You are doing it again. As soon as you are exposed a fraud, your only response is to flame the messenger and resorting to insults.
I don't need to insinuate I'm a competent DBA, because I am, and I don't need your flames.
Sadly your real knowledge is reciprocal to your ego.
You are always boasting here about your credentials, you are even boasting about a book you wrote yourself. You lack any sign of modesty.
Why don't you just admit you stand corrected?
Why do you boast about credentials no one can verify? Maybe you are an 'Adjunct Professor Emeritus', maybe you were kicked out after one term? Who can tell?
You have no right to make any assertions about me, because you don't know me.
Yet who you are is quite known: you are a pompous, arrogant man, with an ego as big as the Empire State Building, who really believes he is the best DBA in the world ('Savvy professional' you usually call it).
Sadly to say so, you are not. Referring to your 'Tuning Oracle with Statspack' none of the scripts you published will work when there are 'holes' in the snapshot id, or the database hase been shut down.

You are using 'Oracle Press Author' as a quality sign, tacitly leaving out 'Oracle Press' has nothing to do with Oracle, as is not published by Oracle.
Unfortunately there are too many newbies here who don't know this, and don't say you aren't using the label 'Oracle Press author' precisely because of this reason.

You have dishonesty inscribed all over you. You are constantly lying.

You know the advice you provide is incorrect, yet you go on over and over and over and over again to publish it here, everything linked to your own website.
This is because you misuse this forum as your primary marketing tool.

Actually you are just a troll
http://en.wikipedia.org/wiki/Troll_%28Internet%29

--
Sybrand Bakker
Senior Oracle DBA
588568
sybrandb:

It was quite shocking to learn from Jonathan Lewis that you really do exist. Shocking and mind-numbing: you really are employed by someone... I'm genuinely aghast.

You dislike Don. Fair enough: it's allowed. But to (a) have a go at him for linking to his sites, when others do likewise, without a murmur of reproval from you and (b) calling him a troll when many, many of your own postings are each a masterclass in nastiness and designed to offend, causes my vision to blur and small, multicoloured dots to dance before my eyes!
Hans Forbrich
Don, Sybrand,

Please take the personal discussion to a non-Oracle site, or to a separate thread. The current discussion no longer contributes toward a solution to the OP's question.

The Community Suggestions and Feedback forum might be a better place.

/Hans
587671
Guys, Well im sorry to start this thread now.. (Arguing).. Anyway thanks for all of your suggestions and help.

For us newbie’s, this is a great place for information and assistance. Even when there’s arguing!!!
Jonathan Lewis
How can I tell if my Db indexes require rebuilding.. when should I rebuild.. ?
HoLy_PiLgRiM

Just above the list of posts there is an Icon of a magnifying glass with the label "Search Forum". For general purpose questions it's always worth calling on this feature to see if it gets you any interesting material.

For your question, I simply typed "rebuild indexes" as the search requirements and left all other options to default. These were the first three threads in the result list:


Rebuild Multiple Indexes 2117495

When to rebuild indexese 2171263

Script: Lists All Indexes that Benefit from a Rebuild 2190191


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
Richard Foote
I see there's been the typical nonsense in this thread regarding your question ...

Check out the presentation on when to rebuild indexes at:

http://www.miracleas.dk/index.asp?page=169&page2=276&page3=179

Cheers

Richard Foote
Richard Foote
Hi Don
>
MetaLink and the docs are the "official" word from
Oracle, right? When I quote my notes, people
chastise me for NOT citing the docs! I can't win!
Point though is Don, metalink documents can be erroneous and are being corrected all the time ...
However, deletes are a different story. Physically,
Oracle indexes are always balanced because empty
blocks stay inside the tree structure after a massive
delete. Logically, Oracle indexes are not
self-balancing because Oracle does not remove the
dead blocks as they become empty.
When a leaf page contains nothing but deleted entries, the leaf block is placed on the freelist and can be subsequently recycled. Therefore, if the insert rate approximates the delete rate and deletes "empty" leaf blocks, the index remains both physically and logically balanced ...

>
This type of “sparse” index is typical of an index on
highly-active tables with large-scale inserts,
deletes and updates. We may have thousands of empty
or near-empty index blocks, and several Oracle
execution plans will run longer on this type of
“sparse” index
It's actually very untypical because you don't appear to understand how deleted entries are cleaned out and blocks recycled by Oracle ...
Excellent question. The benefits of large blocksizes
are demonstrated on this OTN thread where we see a
demo showing 3x faster performance using a larger
block size:

2005
66662&tstart=15&start=12
Is that the same thread where you thought the example was based on the guys actual experience but he was simply quoting from our Russian mate on the Ask Tom website that had absolutely no technical merit and that you eventually agreed by stating "Yeah, I redacted that one. " in this thread

2185234

Well look, so it is ...

>
Also, Robin Schumacher has proved that indexes build
differently in different blocksizes:

http://www.rampant-books.com/book_2003_1_perf.htm

“As you can see, the amount of logical reads has been
reduced in half simply by using the new 16K
tablespace and accompanying 16K data cache.

Clearly, the benefits of properly using the new data
caches and multi-block tablespace feature of Oracle9i
and above are worth your investigation and trials in
your own database.“
You always, always always always, come back to poor Robin's example. The fact it's published in one of your books does rather explain a lot ...

Have you thought of a suitable response yet to Greg Rahn's example where he kinda blows this theory out of the water and where I also show you why it's all nonsensical:

2190675

Thought not ;)

Cheers

Richard Foote
1 - 26
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 27 2013
Added on Dec 7 2002
20 comments
103,427 views