Reputation
13,615
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 23 38
Newest
 Caucus
Impact
~716k people reached

Jul
15
answered Oracle Autotrace vs Explain
Jul
15
comment Oracle Autotrace vs Explain
What "traceroute" are you using? Normally, traceroute is an application that tracks network hops not something dealing with query plans. Are you comparing actual vs. estimated query plans? Or is this some third party utility with an unfortunately confusing name? As an aside, I would not say that B is obviously more efficient than A. It's certainly possible that's the case. But a cost that is too low is just as likely to indicate a problematic query plan as a cost that is too high.
Jul
13
comment How do I prevent an ORA-08102 error
Have you contacted Oracle support? The fact that you're getting corrupted index structures often enough to have solutions that work "sometimes" and to be looking to prevent it implies that you have an underlying problem that needs to be diagnosed. You could try dropping and recreating the index rather than just rebuilding it. You could try rebuilding the table and then rebuilding the indexes. But I'd definitely get a support ticket opened with the trace file(s) you're getting.
Jul
13
comment Oracle DataGuard
I'm not sure that I understand what you are asking. Oracle provides three Data Guard modes (maximum availability, maximum performance, and maximum protection). If one was preferred, there would be no point to offering the other two. Each of the options comes with trade-offs that you need to understand. Which trade-offs you prefer is a very project- and environment-specific question. No one can tell you, for example, whether the performance hit that you get from running in maximum availability is acceptable to your customers running your applications in your environment.
Jul
13
comment Oracle far sync in 12c
What metric are you using to measure "efficient"? What does "very less compute" mean in this context? The benefit of adding a far sync instance is that it runs physically close to the primary and ships redo off to standby databases that are further away so you can run more aggressive DataGuard settings on the primary with less of a performance impact on the primary than you'd get if the redo had to be shipped to the remote standbys synchronously. How much of an advantage that is depends heavily on the particulars of your environment.
Jul
4
comment Snooping-safe Vote tables
@mustaccio - Good catch. I mistakenly thought this was tagged for Oracle.
Jul
4
comment Snooping-safe Vote tables
Does "admin" in this context mean "DBA"? Or "application administrator"? It's possible to prevent DBAs from reading certain bits of data but it will involve installing and configuring Database Vault (in addition to licensing it). Application administrators are much easier to deal with, you can just create an appropriate VPD policy.
Jul
3
comment How to download Oracle Database 11g release 2 for Mac OS
I don't believe Oracle 11.2 was ever built for the Mac. If you want to run Oracle, I believe you'd need to run it inside a virtual machine that would itself be running, presumably, either linux or Windows.
Jul
1
comment Formatting a DATE type in an Oracle database to only accept MMDD
0 isn't a valid year. You could have 1 BC or 1 AD. There is a decent chance that either would confuse whoever has to support this application in the future.
Jul
1
answered Formatting a DATE type in an Oracle database to only accept MMDD
Jun
30
comment Whitespace Trimming on Before Trigger in PL/SQL
You want :new.order_id. The leading colon is necessary. You also want a row-level trigger rather than a statement level trigger so you'd want for each row before your begin. Finally, referencing old as old new as new doesn't accomplish anything. I'd suggest removing it.
Jun
24
comment PLSQL - getting real-time updates
The insert would need to be done by a function defined as an autonomous transaction. Otherwise, it wouldn't be visible outside the scope of your transaction. Of course, that means that the changes wouldn't be rolled back if the parent transaction rolls back.
Jun
24
answered PLSQL - getting real-time updates
Jun
22
answered Is index performance on a TIMESTAMP(6) column impacted when converting it to a number
Jun
22
comment Is index performance on a TIMESTAMP(6) column impacted when converting it to a number
Oracle doesn't have a datetime data type so I assume you're talking about a column defined as a date. How are you using the index? If you want to filter data based on a predicate defined on my_date, you wouldn't want to have any function calls around my_date. Or you'd want to have a function-based index on the full expression.
Jun
22
comment Will an update in my trigger trigger itself? Oracle PLSQL
Why is the table denormalized to store the root_id for all the child rows? Why wouldn't the children just point at the parents and then query the root_id from the ultimate parent? How is a root_id different than a parent_id? Is that just a node that doesn't have a parent_id? If so, the root_id itself would seem redundant-- root nodes would be any node where the parent_id IS NULL.
Jun
22
comment Will an update in my trigger trigger itself? Oracle PLSQL
First, it would generate a mutating table exception. If you worked around the mutating table exception, it would presumably generate an infinite loop. If the data in one row is dependent upon the keys in a different row, you have a normalization problem that you should be solving.
Jun
22
answered Will an update in my trigger trigger itself? Oracle PLSQL
Jun
19
comment List full text indexes in Oracle database
What do you define as a full text index in Oracle? Are you talking about listing the Oracle Text indexes?
Jun
17
comment Table size unaffected after deleting 400k rows
@user1 - That's hard to answer-- you, presumably, know your system and you, presumably, have tested the change in your lower environment. You're in a much better position to know if you need to do anything else. You know, for example, what sort of tablespace the table is in. You know whether your applications depend on the rowid being stable which wouldn't be the case during the shrink operation. You know whether the physical reorganization is going to impact application performance and whether that is an acceptable tradeoff.