Просторы интернета

19 Февраль 2015
Earlier
Вт, Сен 19, 2017

Here's a little oddity that came up recently on the OTN database forum – an example where a “with” subquery (common table expression / factored subquery) produced a different execution plan from the equivalent statement with the subquery moved to an inline view; tested in 12.1.0.2 and 12.2.0.1. Here are [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 14, 2017
with tst as ( select 'qwe word1 asd ...............' s from dual union all select 'qwe word1 asd word2 .........' s from dual union all select 'qwe word1 asd word2 zxc word3' s from dual union all select 'qwe word2 asd word1 zxc [...]
Source: orasql.org
Пт, Сен 01, 2017
Proxy User Authentication in Oracle

Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).

With proxy login, the proxy user can act as if it is the client user with all of the client user's access rights, without [...]

Source: www.oratable.com
Ср, Авг 16, 2017
XMLTABLE to Convert XML To Relational Data

Here's a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.

This solution uses the standard EMP table – the same can be extended to work with any XMLTYPE-relational mapping.

Problem Statement: Receive XML Payload, Parse and Store in Relational Table

An application receives employee data [...]

Source: www.oratable.com
Пн, Авг 14, 2017

A few years ago a bug relating to join elimination showed up in a comment to a post I'd done about the need to keep on testing and learining. The bug was visible in version 11.2.0.2 and, with a script to replay it, I'd found that it had disappeared [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 11, 2017
Dynamic SQL

A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.

So, when should we use dynamic SQL?

Oracle documentation tells us that we need dynamic SQL to run:

SQL whose text is unknown at compile [...]
Source: www.oratable.com
Чт, Авг 10, 2017

I've reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving

Compiled [...]

Source: orasql.org
Чт, Авг 03, 2017

One of the special events that can make it necessary to rebuild an index is the case of the “massive DML”, typically a bulk delete that purges old data from a table. You may even find cases where it's a good idea to mark a couple of your indexes as [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 26, 2017

Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.

Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.

You can come [...]

Source: www.oratable.com
Вт, Июл 25, 2017

This posting was prompted by a tweet from Kamil Stawiarski in response to a question about how he'd discovered the meaning of Redo Op Codes 5.1 and 11.6 – and credited me and Julian Dyke with “the hardest part”.

Over the years I've accumulated (from Julian Dyke, or odd MoS [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 24, 2017

The following is the text of an article I published in the UKOUG magazine several years ago (2010), but I came across it recently while writing up some notes for a presentation and thought it would be worth repeating here.

Fast Now, Fast Later

The title of this piece came from a [...]

Source: jonathanlewis.wordpress.com
Сб, Июл 22, 2017

This is the second of two items that my mother typed out more than 25 years ago. I had very mixed emotions when reading it but ultimately I felt that it was a reminder that, despite all the nasty incidents and stupid behaviour hyped up by the press and news [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 14, 2017
В PDB snapshot copy окружении версии 12.1.0.2: Артём Горбик показал странно отображаемую сессию-блокер: — формально работающую в CDB$ROOT: , и в то же время выполняющую Scheduler Job в контейнере-клоне: Модифицировал под это дело скрипт (с суффиксом _CDB), добавив к инстансу/сессии (INST#/SID#4) номер контейнера — CON#: — и в выводе скрипта [...]
Source: iusoltsev.wordpress.com
Чт, Июл 13, 2017
на physical standby / ADG instance в виде: , как нам с Максимом Филатовым удалось выяснить, вызывается установкой параметра: и, соответственно, лечится (по рекомендации техподдержки): В общедоступном описании бага: о параметре не упомянуто, в то время как в документах типа ORA-1555 Error With Query Duration as 0 Seconds (Doc ID [...]
Source: iusoltsev.wordpress.com
Пт, Июл 07, 2017

The title is a well-known shorthand for parameter optimizer_features_enable and it has been the topic of a recent blog post by Mike Dietrich in which he decries the practice of switching the parameter back to an older version on an upgrade (even though, as he points out, Oracle support [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 05, 2017

My mother died a few weeks ago after a couple of months in terminal care. One of my tasks while she was in care was to go through all her paperwork and while doing so I discovered a couple of stories from her past that she had typed out (remember [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 28, 2017
SQLCODE and SQLERRM

SQLCODE and SQLERRM are Oracle's built-in error reporting functions in PL/SQL.

When an error occurs in PL/SQL at runtime:

SQLCODE returns the number of the last encountered error.

SQLERRM returns the message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current [...]

Source: www.oratable.com
Пт, Июн 16, 2017
Check if JVM is Installed in Oracle

I recently thought of using Java's built-in BigInteger.gcd() method inside a PL/SQL wrapper to calculate GCD in SQLs.

And so I wrote the Java code and ran it on Oracle XE.

Things didn't go as planned.

SQL> -- Create java source for GCD calculation SQL> create or replace and resolve java [...]
Source: www.oratable.com
Ср, Июн 14, 2017

An interesting observation appeared recently as a side-channel on a question on the OTN database forum – how does Oracle execute an unpivot() operation. Here's an example of such a query:

rem rem Script: unpivot_2.sql rem [...]
Source: jonathanlewis.wordpress.com
Вт, Июн 13, 2017
Oracle XML Object-Relational Storage

After the post on models for storing XML data in Oracle and examples of binary XML storage, here's a detailed look at the rigorous and performant structured XML storage or object-relational storage.

Object-relational storage of XML data is based on "shredding" the XML content into a set of SQL [...]

Source: www.oratable.com
Пн, Июн 12, 2017

If you're familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this [...]

Source: jonathanlewis.wordpress.com
Пт, Июн 09, 2017

At the end of my presentation to the UKOUG Database SIG yesterday I summed up (most) of points I'd made with a slide making the claim:

In 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 07, 2017
Oracle XDB Binary XML Storage

The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.

This post shows working examples of binary XML storage

1.1. without XML Schema
1.2 with XML Schema

The scripts below [...]
Source: www.oratable.com
Пн, Июн 05, 2017
Storing XML Data in Oracle

Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table, or an XMLType column in a relational table, to persist XML data.

Different storage models are available in Oracle to best fit the nature of the XML data [...]

Source: www.oratable.com
Чт, Июн 01, 2017

While reading a blog post by Maria Colgan a couple of week ago I came across an observation about histograms that I'd not noticed before; worse still, it was a feature that seemed to make some “damage-limitation” advice I'd been giving for years a really bad idea! The threat [...]

Source: jonathanlewis.wordpress.com
Пн, Май 29, 2017
SQL XML in Oracle: Generating XML from Relational Data

Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.

This post shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.

Let's [...]

Source: www.oratable.com
Чт, Май 25, 2017

Headline – if you don't want to read the note – the /*+ parallel(N) */ hint doesn't mean a query will use parallel execution, even if there are enough parallel execution server processes to make it possible. The parallel(N) hint tells the optimizer to consider the cost of using parallel [...]

Source: jonathanlewis.wordpress.com
Чт, Май 25, 2017
Активно читающий запрос из AWR: , заметность которого, похоже, обусловлена выбором и активным использованием, по-видимому, не самого оптимального плана 2944350538: — ASH TOP5: самые длинные выполнения использовали неудачный план 2944350538 — всё сходится) Сравним Outline удачного и неудачного планов: — отличия в LEADING и методе соединения "RESULT_PAGE_DATA@SEL$4 (*) Из ASH [...]
Source: iusoltsev.wordpress.com
Вт, Май 23, 2017

There's so many things that can go wrong when you start using tables with more than 255 columns – here's one I discovered partly because I was thinking about a client requirement, partly because I had a vague memory of a change in behaviour in 12c and Stefan Koehler [...]

Source: jonathanlewis.wordpress.com
Вс, Май 21, 2017
В дополнение к старому описанию особенностей DS версии 10.2 — Dynamic Sampling при индексном доступе к данным — на том же тесткейсе наблюдал как измененилось поведение Old Style Dynamic Sampling (DS) и Adaptive Dynamic Sampling (ADS) в новых версиях Oracle 12c Итак, тестовый DDL без изменений: 1) Old Style Dynamic [...]
Source: iusoltsev.wordpress.com
Комментирование отключено.