Blogs

Kevin Meade's picture

Dynamic Views, Creating Views based on Variables

articles: 

I have been asked several times recently, how to create "dynamic" views such that a view can be created using "variables". Now I see this question asked on the OraFaq message board, so I thought maybe this is worth posting as an article, so here it is. My answer comes in the form of an Email reply to a friend (why bother to rewrite a prefectly good answer). Please feel free to abuse my answer if you think it deficient, but you better be posting your own alternatives if you do so.

Tuning a 'LIKE-clause' by using Oracle Text or Reverse Key Indexes

articles: 

The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans. This document describes how to tune such SQL statements by making use of Oracle Text or reverse key indexes.

Kevin Meade's picture

Autonomous Transactions: a Poor Mis-Understood Feature

articles: 

In short an Autonomous Transaction lets a job, commit some data to the database as an ON-THE-SIDE event, without committing data in the MAIN-EVENT. Sounds useful and it can be. But the Autonomous Transaction can also be dangerous. An Autonomous Transaction is kind of like a teenage daughter (I have two) "Oh DAD! You just don't understand me". A lack of understanding is a foundation for trouble. Maybe we can’t understand our teenagers, but we can understand Autonomous Transactions a little better.

Partitioning in Oracle 11g

articles: 

Partitioning is one of the most sought after options for data warehousing. Almost all Oracle data warehouses use partitioning to improve the performance of queries and also to ease the day-to-day maintenance complexities. Starting with 11G, more partitioning options have been provided and these should reduce the burden of the DBA to a great extent.

vjain's picture

Building a Dynamic Oracle ETL Procedure

articles: 

If you are on Oracle 9i or higher, you have external tables and pipelined table functions available to meet your ETL needs. But in order to utilize these tools in a dynamic ETL environment, you need to design PL/SQL procedures that can support the loading of files dynamically. This article will provide you with one design that has proven to be very robust and scalable.

vjain's picture

Materialized View Fast Refreshes are Slow

articles: 

A materialized view that is verified to be fast refresh should update relatively fast. But, what happens when there are few changes to the master table, no network issues, no aggregation in the snapshot query and the refresh still runs slow?

sethunathu's picture

Sizing your undo tablespace

articles: 

It is always a puzzle for a DBA to look into the user's complaint of getting "ORA-01555 Snapshot too old : rollback segment number x with name "_SYSSMUx$" too small " error. You have looked into the database. If your UNDO_MANAGEMENT is set to AUTO, you can not do anything to size the rollback segments manually since it is being managed by oracle. All the associated tables and indexes have been analyzed and statistics are up to date. The undo tabelspace is almost full. You may advise the user that there should be frequent commits (if it is a data loading process) or if there is a long running query and other users change the data that is being selected by the query, this can happen and in that case, if possible, advise the user not to run these two at the same time.

Art Trifonov's picture

Making Use of Table Functions -- Part II

articles: 

In Part I we discussed views, stored procedures, and table functions as the three options of returning record sets from the code stored in the database. In this section I will concentrate on table functions, demonstrating several kinds of solutions that utilize this feature.

Art Trifonov's picture

Making Use of Table Functions -- Part I

articles: 

Having in my practice found many useful applications for table functions I am a big fan of this feature. Unfortunately, I don’t think it always gets the attention it deserves. Oracle documentation does a decent job of explaining how to code table functions, but the use cases they provide can lead one to believe this feature has no universal application and is meant to solve just a few specific kinds of problems.

Kevin Meade's picture

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

articles: 

So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document. So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT.

Pages

Subscribe to RSS - blogs