Oracle Interview Question Help Plz ..

[google2011]

interview lo nannu indexes anni columns paina create cheyacha ani adigaru ..

nenu avasaram ledu only user ee columns paina query chestaro vati paina create cheste chalu ani cheppina ...

thanu anni columns paina create cheste emavutundi ani adigindi ... use em undadu create chesna ani cheppina


nen cheppindi correct aa wrong aa ??

indexes anni columns paina create cheste enti use ??

[150bryant]

indexes are created to retrieve data faster from the DB. We can create indexes on all columns but there is no point in doing so.
nee answer correctey....let see if someone has a better answer

[taadu.....bongaram]

usual ga primary keys meede antaru kada........ kaada??

[google2011]

[quote name='jamajacha' timestamp='1335452205' post='1301690703']
indexes are created to retrieve data faster from the DB. We can create indexes on all columns but there is no point in doing so.
nee answer correctey....let see if someone has a better answer
[/quote]


yeah .. adhe cheppa nen kuda .. thanu are u sure ani 2 times adigindi .. so naku dbt achindi ..

[google2011]

[quote name='taadu.....bongaram' timestamp='1335452277' post='1301690713']
usual ga primary keys meede antaru kada........ kaada??
[/quote]


ala em kadu ankunta ... manam general ga ee columns ekkuva use chestamo vati paina create cheyachu ... am not sure though ..

[mtkr]

dis advantage untadiii....

[Help veerudu]

i have a sql query.....both joins and sub queries same ahaaaa?
nenu aitay same anukuntunna....i need exact answer.

[nenu.unanu]

this question dpends on how the table is accessed..how frequently it is accessed...how big is the table
My best answer would be I would copy the table (if it's on production database) to new Dev environment and play with the indexes.
then generate a explain plan on that table..which will tell me the best optimizer plan to use indexes on the table.
You can even execute statspach report to see the query on that table

[150bryant]

It depends... If you do mostly queries like in a data warehouse, it is beneficial to create all possible indexes. If you have more of an OLTP workload, you only want to create the indexes that you really need due to the added index maintenance overhead.

Furthermore, I wouldn't simply create indexes on all columns. It may be better to have an index on a combination of columns and you may not need indexes on some columns. Therefore, a good start would be to run your SQL statements as workload through the Design Advisor and see what indexes are suggested by that tool. The next step is to understand how indexes work (e.g. what is index-only access, prefetching, include columns, just to name very few) and then apply this knowledge to your workload.

[google2011]

[quote name='mtkr' timestamp='1335452486' post='1301690746']
dis advantage untadiii....
[/quote]


what is the disadvantage.. anyways we'll drop the indexes before loading the data and create the indexes after loading kada .. so what is the disadvantage ??

[150bryant]

[quote name='google2011' timestamp='1335452608' post='1301690762']


what is the disadvantage.. anyways we'll drop the indexes before loading the data and create the indexes after loading kada .. so what is the disadvantage ??
[/quote]Performance wise ga there will be a Big HIT. as it has to maintain all indexes.

[Help veerudu]

[quote name='Help veerudu' timestamp='1335452556' post='1301690754']
i have a sql query.....both joins and sub queries same ahaaaa?
nenu aitay same anukuntunna....i need exact answer.
[/quote]


avari dagara aina ee query ki answer untay plz post cheyandi!!

[google2011]

[quote name='jamajacha' timestamp='1335452604' post='1301690760']
It depends... If you do mostly queries like in a data warehouse, it is beneficial to create all possible indexes. If you have more of an OLTP workload, you only want to create the indexes that you really need due to the added index maintenance overhead.

Furthermore, I wouldn't simply create indexes on all columns. It may be better to have an index on a combination of columns and you may not need indexes on some columns. Therefore, a good start would be to run your SQL statements as workload through the Design Advisor and see what indexes are suggested by that tool. The next step is to understand how indexes work (e.g. what is index-only access, prefetching, include columns, just to name very few) and then apply this knowledge to your workload.
[/quote]

ee link naku cheppava plz ???

[informatica86]

[quote name='google2011' timestamp='1335452608' post='1301690762']


what is the disadvantage.. anyways we'll drop the indexes before loading the data and create the indexes after loading kada .. so what is the disadvantage ??
[/quote]




1)it will eat away space in the DB

2)updates will be slow on that table, when ever the recor is updated, it have to update index data portion also resulting in slow performance

[mtkr]

[quote name='google2011' timestamp='1335452608' post='1301690762']


what is the disadvantage.. anyways we'll drop the indexes before loading the data and create the indexes after loading kada .. so what is the disadvantage ??
[/quote]


update appudu slow untadi....
updating a column which has index on it is slower than updating a column which doesn't hav index on it...