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

19 Февраль 2015
Вс, Ноя 19, 2017

In one of the previous posts I showed How even empty trigger increases redo generation, but running the test from that post, I have found that this behaviour a bit changed on 12.2:
In my old test case, values of column A were equal to values of B, [...]

Source: orasql.org
Вс, Ноя 19, 2017

On versions – Oracle uses objects' statistics to make direct path reads decision (of course, if “_direct_read_decision_statistics_driven” haven't changed it to “false”), and we can force serial direct reads on statement level using sql profiles with hints INDEX_STATS/TABLES_STATS, but since at least this decision ignores statistics.

Btw, thanks [...]

Source: orasql.org
Чт, Ноя 16, 2017
JSON in the database

JSON is a simple data interchange format, an alternative to XML that's gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.

Here's a [...]

Source: www.oratable.com
Чт, Ноя 16, 2017
select s.module ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch from v$sql s where s.rows_processed>100 and s.executions >1 and s.fetches >1 and s.module is not null and s.command_type = 3 -- SELECTs only and s.program_id = 0 [...]
Source: orasql.org
Сб, Ноя 11, 2017
Полное атомарное обновление матвью / Complete Atomic MV Refresh: большую часть времени тратит на непараллельное выполнение запроса на вставку: , который в свою очередь, в основном ожидает db file scattered read: — что нормально и непротиворечиво Однако, отдельное выполнение того же запроса из SQL*Plus происходит почти в 5 раз быстрее: [...]
Source: iusoltsev.wordpress.com
Пт, Ноя 10, 2017

Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.

What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not [...]

Source: www.oratable.com
Вс, Окт 22, 2017
При установленном параметре: параллельное построение индекса легко распеределяется между нодами RAC: , несмотря на исправленный Bug 16458105 — PARALLEL_FORCE_LOCAL=true not honoured for ALTER INDEX — superseded (Doc ID 16458105.8): — что отражено/соответствует документу Patch Set — List of Bug Fixes by Problem Type (Doc ID 1683802.1) Реклама Filed under: [...]
Source: iusoltsev.wordpress.com
Пн, Окт 02, 2017

Unfortunately associative arrays still require more “coding”:
we still can't use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don't have convinient iterators and even function to get all keys…
That's why I want to show my templates for such things like iterator [...]

Source: orasql.org
Пт, Сен 29, 2017

I see quite often when developers ask questions about connected components:

Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups: (1, [...]
Source: orasql.org
Ср, Сен 27, 2017

I've troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ersand instead of :colon! I have never seen this before and I couldn't find anything about this in documentation…
Unfortunately SQL*Plus doesn't support ampersand yet, even if you disable define (“set define [...]

Source: orasql.org
Пн, Сен 25, 2017
Stored Procedures in Packages

Typical scenario: in a project's design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored: packageX gets split into packageY and packageZ. All goes well – the application gets deployed and is running merrily – till a change request comes in.

A new developer refers [...]

Source: www.oratable.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
Пт, Авг 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
Ср, Июл 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
Пт, Июл 14, 2017
В PDB snapshot copy окружении версии Артём Горбик показал странно отображаемую сессию-блокер: — формально работающую в 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
Ср, Июн 28, 2017

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
Вт, Июн 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
Ср, Июн 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
Пн, Май 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
Активно читающий запрос из AWR: , заметность которого, похоже, обусловлена выбором и активным использованием, по-видимому, не самого оптимального плана 2944350538: — ASH TOP5: самые длинные выполнения использовали неудачный план 2944350538 — всё сходится) Сравним Outline удачного и неудачного планов: — отличия в LEADING и методе соединения "RESULT_PAGE_DATA@SEL$4 (*) Из ASH [...]
Source: iusoltsev.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
Чт, Май 18, 2017
Fuzzy Match using UTL_MATCH

Oracle's UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms:

Levenshtein Distance Jaro-Winkler Distance

Let's understand the algorithms and see UTL_MATCH subprograms in action.

Note: It is not necessary to understand the nitty-gritties of the algorithms to use UTL_MATCH. The deep dive is for [...]

Source: www.oratable.com
Пн, Май 15, 2017
SQL Find Differences between Two Tables

In a recent project, a "shadow" table had to be compared periodically with its main source table to identify the differences between the two tables.

The nature of differences fell into one of these buckets:

Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: [...]
Source: www.oratable.com
Вс, Май 14, 2017
, т.е. в случаях, когда использование VARCHAR2-функций типа LISTAGG заканчивается LISTAGG_CLOB Использовавшаяся поначалу User-Defined Aggregate Function типа LISTAGG_CLOB в плане скорости выполнения показывала самые грустные рез-ты: *) — пересылая по SQL*Net 50MB клиенту и 27MB обратно(!), совершая 400,000(!) roundtrip-ов, что, вероятно, является неприятной особенностью передачи CLOB данных и коственно подтверждается [...]
Source: iusoltsev.wordpress.com
Комментирование отключено.