Entries in Development (4)
Tuning Leading '%' Queries - a simple approach
Karl Reitschuster
in Development, Tuning
|
Post a Comment
| 
Email
Hi reader,
usually queries using LIKE and leading '%' cannot be indexed. So an idea would be to mirror the string content with the string reverse function to be able to put the '%' operator at the end :
Cardinality and Pipelined Functions - Ugly!
Karl Reitschuster
in Development, Tuning
|
Post a Comment
| Email
Dear reader,
pipelined functions are very comfortable to process incoming data and to return it as structured result set. For this reason they are frequently used in SQL; But be carefully if you join result sets from pipelined function with other tables.
The Oracle optimizer usually uses gathered table statistics to calculate the cardinality of a join operation; For a result set originated in a pipeline function it has no statistics to evaluate and hence could try to get another path to the data.
Following SQL retrieves internal employee GUID's from external Id's passed as comma delimited string.
IT starts with the data type
Karl Reitschuster
in Design, Development
|
2 Comments
|
2 References
| Email
Intro
Examples
* Mathematics with Account numbers
* Timestamp as order citeria, unique history sequence
* The ultimate is_number - function
Impact of choosing the wrong datatype
* Optimizer miss-assumptions
* Breakdown of data interfaces
* Loss of service
Conclusion
Successfully launch of a new data interface or The egg preceded the chicken
Karl Reitschuster
in Development, Tuning
|
Post a Comment
|
1 Reference
| Email
Dear reader, i created a data interface for new demand of incoming data exchange from another system. Finally before the new Interface was released to production i took some tests on a production clone database. Usually in development the interface processed 6m rows in about 15 minutes. But on the clone database the processing did not end after hours. After some analysis i found out that the interface related tables had statistics gathered before processing and resulted in 0 rows per interface table.
