نوشته های مسعود سلطانی راد

Oracle AI Database 26ai؛ انقلابی در مدیریت داده با هوش مصنوعی یکپارچه

مسعود سلطانی راد — Thu, 01 Jan 2026 08:47:14 +0330

مقدمهبا رشد انفجاری داده‌ها و ورود هوش مصنوعی به هسته تصمیم‌سازی سازمان‌ها، پایگاه داده‌ها دیگر نمی‌توانند صرفاً محل ذخیره اطلاعات باشند. امروز کسب‌وکارها به زیرساختی نیاز دارند که داده، تحلیل و هوش مصنوعی را به‌صورت یکپارچه، امن و مقیاس‌پذیر ارائه دهد.Oracle AI Database 26ai پاسخی مستقیم به این نیاز است؛ نسخه‌ای که هوش مصنوعی را به‌صورت معماری‌شده در قلب پایگاه داده قرار می‌دهد و فصل جدیدی در مدیریت داده سازمانی رقم می‌زند.Oracle AI Database 26ai چیست؟Oracle AI Database 26ai جدیدترین نسخه پایگاه داده پرچم‌دار اوراکل و تحقق عملی چشم‌انداز AI for Data است. در این نسخه، هوش مصنوعی نه به‌عنوان یک ابزار جانبی، بلکه به‌عنوان بخشی از Database Engine طراحی شده است.این پایگاه داده امکان استفاده از AI را روی تمام انواع داده فراهم می‌کند:داده‌های تراکنشی (OLTP)داده‌های تحلیلی (OLAP)داده‌های بدون ساختارداده‌های برداری (Vector)داده‌های JSON، Graph و Spatialنتیجه این رویکرد، حذف معماری‌های پیچیده و اجرای هوش مصنوعی مستقیماً کنار داده است؛ بدون نیاز به جابه‌جایی، کپی یا Exposure اطلاعات.معماری AI for Data؛ هوش مصنوعی در تمام لایه‌هاOracle AI Database 26ai هوش مصنوعی را در کل زنجیره داده و توسعه نرم‌افزار تزریق کرده است:هوش مصنوعی برای مدیریت دادهپایگاه داده از AI برای بهینه‌سازی عملکرد، مدیریت منابع، افزایش پایداری و کاهش پیچیدگی عملیاتی استفاده می‌کند.هوش مصنوعی برای تحلیل و جستجوبا AI Vector Search، امکان جستجوی معنایی و تحلیلی روی داده‌های ساخت‌یافته و بدون ساختار فراهم می‌شود؛ قابلیتی کلیدی برای سناریوهای RAG، تحلیل اسناد، تصاویر و محتوای چندرسانه‌ای.هوش مصنوعی برای توسعه اپلیکیشنتوسعه‌دهندگان می‌توانند با استفاده از مدل داده یکپارچه، Data Annotation و Agentهای هوشمند، اپلیکیشن‌های AI-محور را سریع‌تر و ایمن‌تر توسعه دهند.Oracle Autonomous AI Lakehouse و Apache Icebergیکی از مهم‌ترین تحولات Oracle 26ai، معرفی Oracle Autonomous AI Lakehouse است؛ راهکاری که مرز میان Data Warehouse و Data Lake را از بین می‌برد.ویژگی‌های کلیدی:پشتیبانی کامل از فرمت متن‌باز Apache Icebergاجرای Query واحد روی داده‌های عملیاتی و تحلیلیقابلیت اجرا در OCI، AWS، Azure و Google Cloudسازگاری کامل با Databricks و Snowflakeمقیاس‌پذیری Serverless و پرداخت به‌ازای مصرفعملکرد بسیار بالا با معماری Exadataاین معماری به سازمان‌ها اجازه می‌دهد بدون تغییر سرمایه‌گذاری‌های قبلی، از قدرت AI روی داده‌های Data Lake استفاده کنند.Agentic AI؛ عامل‌های هوشمند درون پایگاه دادهOracle AI Database 26ai مفهوم Agentic AI را مستقیماً وارد پایگاه داده کرده است. این Agentها می‌توانند:استدلال چندمرحله‌ای انجام دهندمسیرهای مختلف حل مسئله را بررسی کننددر صورت نیاز داده‌های بیشتری درخواست کنندو در نهایت پاسخ یا اقدام دقیق‌تری ارائه دهندپشتیبانی از Model Context Protocol (MCP) و ابزارهایی مانند AI Private Agent Factory امکان ساخت و استقرار Agentها را حتی بدون کدنویسی فراهم می‌کند؛ آن هم با کنترل کامل امنیت و دسترسی.جستجوی برداری یکپارچه (Unified Hybrid Vector Search)Oracle 26ai جستجوی برداری را با سایر مدل‌های داده ترکیب می‌کند:RelationalJSONTextGraphSpatialاین قابلیت امکان تحلیل هم‌زمان داده‌های متنوع را فراهم می‌کند و پایگاه داده را به هسته اصلی سناریوهای هوش مصنوعی سازمانی تبدیل می‌سازد.امنیت نسل آینده؛ مقاوم در برابر تهدیدات کوانتومیامنیت در Oracle AI Database 26ai فقط یک قابلیت نیست؛ یک اصل معماری است. این نسخه از:الگوریتم‌های تأییدشده NIST (ML-KEM) برای رمزنگاری داده‌های در حال انتقالرمزنگاری مقاوم در برابر کوانتوم برای داده‌های ذخیره‌شدهاستفاده می‌کند.در کنار آن، قابلیت‌هایی مانند:SQL FirewallMasking پویا در سطح Row، Column و Cellکنترل دقیق دسترسی AI به دادهامنیت داده‌ها را در بالاترین سطح تضمین می‌کنند.Oracle Exadata؛ شتاب‌دهنده واقعی هوش مصنوعیOracle Exadata در 26ai نقشی کلیدی در شتاب‌دهی پردازش‌های AI ایفا می‌کند:Offload جستجوی برداری به StorageRDMA با تأخیر بسیار کمExadata Exascale برای مقیاس‌پذیری بالاTiering هوشمند بین Memory، Flash و Diskفشرده‌سازی HCC برای کاهش هزینه ذخیره‌سازیمهاجرت آسان و بدون ریسکOracle AI Database 26ai یک نسخه Long-Term Support است که جایگزین Oracle Database 23ai می‌شود.مشتریان می‌توانند تنها با اعمال Release Update:بدون Upgrade پیچیدهبدون Re-Certification اپلیکیشن بدون هزینه اضافی برای قابلیت‌های AI از امکانات جدید استفاده کنند.جمع‌بندیOracle AI Database 26ai یک نسخه جدید نیست؛بلکه تعریف جدیدی از پایگاه داده سازمانی است.در این معماری، داده و هوش مصنوعی در یک بستر واحد، امن و مقیاس‌پذیر به هم می‌رسند. برای سازمان‌هایی که آینده خود را بر پایه داده و AI بنا می‌کنند، Oracle 26ai انتخابی استراتژیک و آینده‌نگرانه است.

بررسی قابلیت DBMS_SEARCH در اوراکل 23ai

مسعود سلطانی راد — Thu, 15 May 2025 07:44:30 +0330

مقدمهبا معرفی Oracle Database 23ai، قابلیت جدیدی به نام DBMS_SEARCH اضافه شده است که امکان جستجوی یکپارچه و قدرتمند در داده‌های ذخیره‌شده در پایگاه داده را فراهم می‌کند. این قابلیت به کاربران اجازه می‌دهد تا به راحتی و با سرعت بالا، داده‌های مورد نظر خود را در میان حجم عظیمی از اطلاعات پیدا کنند. در این مقاله، به بررسی این قابلیت و ارائه یک مثال کاربردی می‌پردازیم.DBMS_SEARCH چیست؟DBMS_SEARCH یک پکیج PL/SQL است که امکان جستجوی متن کامل (Full-Text Search) را در پایگاه داده اوراکل فراهم می‌کند. این قابلیت به شما اجازه می‌دهد تا داده‌های متنی را در جدول‌ها و ستون‌های مختلف جستجو کنید و نتایج مرتبط را به سرعت بازیابی کنید. این ویژگی به ویژه در محیط‌هایی که نیاز به جستجوی سریع و دقیق در داده‌های حجیم وجود دارد، بسیار مفید است.مثال کاربردی: جستجوی محصولات در یک فروشگاه آنلاینفرض کنید می‌خواهیم یک سیستم جستجو برای یک فروشگاه آنلاین ایجاد کنیم که به کاربران اجازه می‌دهد محصولات مورد نظر خود را بر اساس نام، توضیحات و ویژگی‌های دیگر جستجو کنند. با استفاده از DBMS_SEARCH، می‌توانیم این سیستم را به راحتی پیاده‌سازی کنیم.مرحله ۱: ایجاد جدول محصولاتابتدا یک جدول با نام products ایجاد می‌کنیم که شامل اطلاعات محصولات است:CREATE TABLE products (product_id NUMBER PRIMARY KEY,product_name VARCHAR2(100),category VARCHAR2(50),price NUMBER,description CLOB);مرحله ۲: درج داده‌هاحالا داده‌های مربوط به محصولات را در جدول درج می‌کنیم:INSERT INTO products (product_id, product_name, category, price, description)VALUES (1, ‘Laptop’, ‘Electronics’, 1200, ‘A high-performance laptop with 16GB RAM and 512GB SSD.’);INSERT INTO products (product_id, product_name, category, price, description)VALUES (2, ‘Smartphone’, ‘Electronics’, 800, ‘A latest model smartphone with 128GB storage and 5G support.’);INSERT INTO products (product_id, product_name, category, price, description)VALUES (3, ‘Tablet’, ‘Electronics’, 600, ‘A lightweight tablet with 10-inch display and 64GB storage.’);مرحله ۳: ایجاد ایندکس جستجوبرای استفاده از DBMS_SEARCH، ابتدا باید یک ایندکس جستجو ایجاد کنیم. این ایندکس به ما اجازه می‌دهد تا داده‌های متنی را به سرعت جستجو کنیم:BEGINDBMS_SEARCH.CREATE_INDEX(‘products_index’);DBMS_SEARCH.ADD_SOURCE(‘products_index’, ‘products’, ‘product_id’, ‘product_name, description’);END;//ارتباط با ماجهت دریافت خدمات مشاوره، آموزش و نگهداری پایگاه داده اوراکل با ما در ارتباط باشدمرحله ۴: جستجوی داده‌هاحالا می‌توانیم از DBMS_SEARCH برای جستجوی محصولات استفاده کنیم. به عنوان مثال، می‌خواهیم تمام محصولاتی را که شامل کلمه “laptop” هستند، پیدا کنیم:DECLAREv_results DBMS_SEARCH.TEXT_RESULT_TABLE;BEGINDBMS_SEARCH.SEARCH(‘products_index’, ‘laptop’, v_results);FOR i IN 1..v_results.COUNT LOOPDBMS_OUTPUT.PUT_LINE(‘Product ID: ‘ || v_results(i).key || ‘, Score: ‘ || v_results(i).score);END LOOP;END;/مرحله ۵: مشاهده نتیجهبا اجرای کد بالا، خروجی زیر را مشاهده خواهید کرد:Product ID: 1, Score: 1این نتیجه نشان می‌دهد که محصول با product_id برابر با ۱ (که یک لپ‌تاپ است) با امتیاز ۱ پیدا شده است.مزایای استفاده از DBMS_SEARCHسرعت بالا: DBMS_SEARCH به شما اجازه می‌دهد تا داده‌های متنی را به سرعت جستجو کنید.یکپارچه‌سازی آسان: این قابلیت به راحتی با PL/SQL و سایر ابزارهای اوراکل یکپارچه می‌شود.قدرت و انعطاف‌پذیری: با استفاده از DBMS_SEARCH، می‌توانید جستجوهای پیچیده و پیشرفته‌ای را انجام دهید.نتیجه‌گیریقابلیت DBMS_SEARCH در Oracle Database 23ai، ابزار قدرتمندی برای جستجوی متن کامل در داده‌های ذخیره‌شده در پایگاه داده فراهم می‌کند. این قابلیت به شما اجازه می‌دهد تا داده‌های مورد نظر خود را به سرعت و با دقت بالا پیدا کنید. با استفاده از مثال ساده‌ای که در این مقاله ارائه شد، می‌توانید به راحتی این قابلیت را در پروژه‌های خود به کار بگیرید.

راهنمای جامع استفاده از Materialized View در PostgreSQL

مسعود سلطانی راد — Sat, 21 Dec 2024 09:09:18 +0330

Materialized View یکی از قابلیت‌های قدرتمند PostgreSQL است که امکان ذخیره فیزیکی نتیجه کوئری‌ها و به‌روزرسانی داده‌ها از جداول پایه به صورت دوره‌ای را فراهم می‌کند. این قابلیت در شرایطی که کوئری‌های پیچیده و زمان‌بر وجود دارند و نیاز به دسترسی سریع‌تر به داده‌ها است، انتخاب بهتری نسبت به View معمولی است. در این مقاله، نحوه ایجاد، به‌روزرسانی و حذف Materialized View‌ها را بررسی خواهیم کرد و مثالی کاربردی ارائه می‌دهیم.معرفیMaterialized ViewدرPostgreSQL، ‌View‌ها جداول مجازی هستند که داده‌های جداول زیرساختی را نمایش می‌دهند. در حالی که ‌View‌های ساده می‌توانند قابل به‌روزرسانی باشند، Materialized View‌ها امکان ذخیره‌سازی داده‌ها به صورت فیزیکی را فراهم می‌کنند. این قابلیت برای کوئری‌های سنگین بسیار مفید است و دسترسی سریع به داده‌ها را ممکن می‌سازد.Materialized View‌ها معمولاً در انبارهای داده (Data Warehouse) و برنامه‌های هوش تجاری (BI) مورد استفاده قرار می‌گیرند.نحوه ایجاد Materialized Viewبرای ایجاد یک Materialized View از دستور زیر استفاده کنید:CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_nameASqueryWITH [NO] DATA;توضیحات:view_name: نام Materialized View را مشخص می‌کند.Query: کوئری که داده‌ها را از جداول پایه بازیابی می‌کند.WITH DATA / WITH NO DATA:WITH DATA: داده‌ها را در زمان ایجاد View بارگذاری می‌کند.WITH NO DATA: بدون بارگذاری داده‌ها View را ایجاد می‌کند و آن را غیرقابل خواندن می‌سازد تا زمانی که داده‌ها بارگذاری شوند.IF NOT EXISTS: تنها در صورتی که View از قبل وجود نداشته باشد، آن را ایجاد می‌کند.به‌روزرسانی داده‌ها در Materialized Viewبرای بارگذاری یا به‌روزرسانی داده‌ها از دستور زیر استفاده کنید:REFRESH MATERIALIZED VIEW view_name;هنگام به‌روزرسانی، PostgreSQL جداول زیرساختی را قفل می‌کند، که این امر ممکن است منجر به تأخیر یا ایجاد اختلال در عملکرد سایر فرآیندها شود. برای مدیریت این مشکل، می‌توان از گزینه CONCURRENTLY استفاده کرد که به PostgreSQL اجازه می‌دهد تا داده‌ها را بدون قفل کردن جداول به‌روزرسانی کند. این روش نیازمند وجود یک شاخص یکتا(UNIQUE INDEX) برای Materialized View است و در مواقعی که عملیات هم‌زمان ضروری است، بسیار مفید خواهد بود. برای جلوگیری از این موضوع، می‌توانید گزینه CONCURRENTLY را استفاده کنید:REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;نکات مهم:استفاده از گزینه CONCURRENTLY نیازمند وجود یک شاخص یکتا (UNIQUE INDEX) برایMaterialized View است.گزینهCONCURRENTLY از نسخه 9.4 به بعد PostgreSQL در دسترس است.حذفMaterialized Viewبرای حذف یک Materialized View از دستور زیر استفاده کنید:حذف یکMaterialized View تنها خود View را از بین می‌برد و داده‌های ذخیره شده در جداول زیرساختی اصلی بدون تغییر باقی می‌مانند. این بدان معناست که داده‌های ذخیره شده به صورت فیزیکی در View حذف می‌شوند، اما تأثیری بر داده‌های اصلی ندارد.DROP MATERIALIZED VIEW view_name;در این دستور، view_name نام Materialized View مورد نظر برای حذف است.مثال عملیایجادMaterialized Viewدر این مثال، Materialized View‌ای به نام rental_by_category ایجاد می‌کنیم که مجموع فروش هر دسته‌بندی را نمایش می‌دهد. این سناریو در محیط‌های تجاری مانند تحلیل فروشگاه‌های آنلاین، برای تعیین دسته‌بندی‌های پرفروش یا بررسی روند فروش محصولات در دسته‌های مختلف، کاربرد دارد:CREATE MATERIALIZED VIEW rental_by_categoryASSELECT c.name AS category,sum(p.amount) AS total_salesFROM (((((payment pJOIN rental r ON ((p.rental_id = r.rental_id)))JOIN inventory i ON ((r.inventory_id = i.inventory_id)))JOIN film f ON ((i.film_id = f.film_id)))JOIN film_category fc ON ((f.film_id = fc.film_id)))JOIN category c ON ((fc.category_id = c.category_id)))GROUP BY c.nameORDER BY sum(p.amount) DESCWITH NO DATA;با توجه به گزینه WITH NO DATA، داده‌ها بارگذاری نمی‌شوند و کوئری گرفتن ازView خطا می‌دهد. برای بارگذاری داده‌ها از دستور زیر استفاده کنید:REFRESH MATERIALIZED VIEW rental_by_category;بازیابی داده‌ها از Materialized ViewSELECT * FROM rental_by_category;خروجی:category | total_sales-------------+-------------Sports | 4892.19Sci-Fi | 4336.01Animation | 4245.31Drama | 4118.46Comedy | 4002.48New | 3966.38Action | 3951.84Foreign | 3934.47Games | 3922.18Family | 3830.15Documentary | 3749.65Horror | 3401.27Classics | 3353.38Children | 3309.39Travel | 3227.36Music | 3071.52به‌روزرسانی داده‌ها با CONCURRENTLYبرای استفاده از این گزینه، ابتدا باید یک شاخص یکتا ایجاد کنید:CREATE UNIQUE INDEX rental_categoryON rental_by_category (category);سپس داده‌ها را به‌روزرسانی کنید:REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;جمع‌بندی· Materialized View داده‌های حاصل از کوئری‌ها را به صورت فیزیکی ذخیره می‌کند.· از دستورCREATE MATERIALIZED VIEW برای ایجاد، REFRESH MATERIALIZED VIEW برای به‌روزرسانی وDROP MATERIALIZED VIEW برای حذف استفاده کنید.· گزینه CONCURRENTLY امکان به‌روزرسانی بدون قفل کردن جداول زیرساختی را فراهم می‌کند.Materialized View‌ها ابزار مفیدی برای بهبود عملکرد کوئری‌های پیچیده و دسترسی سریع به داده‌ها هستند، اما دارای محدودیت‌هایی نیز می‌باشند. برای مثال، داده‌های ذخیره‌شده در Materialized View به‌صورت خودکار به‌روزرسانی نمی‌شوند و نیاز به اجرای دستی یا برنامه‌ریزی‌شده دستور REFRESH دارند. همچنین، استفاده از فضای دیسک برای ذخیره‌سازی داده‌ها ممکن است چالش‌برانگیز باشد. امیدواریم این مقاله راهنمای کاملی برای استفاده از این قابلیت باشد.

CEIL و FLOOR در پایگاه داده Oracle 23ai

مسعود سلطانی راد — Tue, 03 Dec 2024 08:44:23 +0330

در پایگاه داده اوراکل ۲۳ai توابع CEIL و FLOOR از انواع داده های DATE، TIMESTAMP و INTERVAL پشتیبانی می کنند. در نسخه‌های قبلی، توابع CEIL و FLOOR فقط اعداد را می‌پذیرفتند و به ما این امکان را می‌دادند که به ترتیب به نزدیک‌ترین مقدار صحیح به بالا یا پایین گرد کنیم.راه اندازینمونه های این مقاله از جدول زیر استفاده می کنند.drop table if exists t1 purge;create table t1 (id number,number_col number,date_col date,timestamp_col timestamp,interval_col interval day to second);insert into t1 (id, number_col, date_col, timestamp_col, interval_col) values(۱, ۱٫۳, to_date(‘2023-05-01 08:15′,’yyyy-mm-dd hh24:mi’), timestamp ‘2023-05-01 08:15:00.0’, to_dsinterval(‘0 08:15:00’)),(۲, ۱٫۶, to_date(‘2023-05-01 13:45′,’yyyy-mm-dd hh24:mi’), timestamp ‘2023-05-01 13:45:00.0’, to_dsinterval(‘0 13:45:00’));commit;داده ها را در جدول نمایش می دهیم. این نشان دهنده نقطه شروع ما است.alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;alter session set nls_timestamp_format=’yyyy-mm-dd hh24:mi:ss’;set linesize 100column date_col format a20column timestamp_col format a20column interval_col format a30select * from t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱٫۳ ۲۰۲۳-۰۵-۰۱ ۰۸:۱۵:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۸:۱۵:۰۰ +۰۰ ۰۸:۱۵:۰۰٫۰۰۰۰۰۰۲ ۱٫۶ ۲۰۲۳-۰۵-۰۱ ۱۳:۴۵:۰۰ ۲۰۲۳-۰۵-۰۱ ۱۳:۴۵:۰۰ +۰۰ ۱۳:۴۵:۰۰٫۰۰۰۰۰۰SQL>
ارتباط با ماجهت دریافت خدمات مشاوره، آموزش و نگهداری پایگاه داده اوراکل با ما در ارتباط باشدCEILتابع CEIL مقادیر DATE، TIMESTAMP و INTERVAL را گرد می کند. این شبیه به استفاده از تابع ROUND است، اما تابع CEIL همیشه جمع می شود. پارامتر فرمت به طور پیش‌فرض روی «DD» است، بنابراین بدون پارامتر قالب، به روز بعد گرد می‌کنیم.select id,ceil(number_col) as number_col,ceil(date_col) as date_col,ceil(timestamp_col) as timestamp_col,ceil(interval_col) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۲ ۲۰۲۳-۰۵-۰۲ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۲ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۱ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۲ ۲۰۲۳-۰۵-۰۲ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۲ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۱ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>اگر این را با تابع ROUND مقایسه کنیم، می بینیم که بسته به مقدار، ممکن است به سمت بالا یا پایین گرد شود.select id,round(number_col) as number_col,round(date_col) as date_col,round(timestamp_col) as timestamp_col,round(interval_col) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۲ ۲۰۲۳-۰۵-۰۲ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۲ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۱ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>پارامتر فرمت سطح گرد شدن را تعیین می کند. در مثال زیر از “HH24” برای جمع کردن ساعت بعدی استفاده می کنیم.select id,ceil(number_col) as number_col,ceil(date_col, ‘hh24’) as date_col,ceil(timestamp_col, ‘hh24’) as timestamp_col,ceil(interval_col, ‘hh24’) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۲ ۲۰۲۳-۰۵-۰۱ ۰۹:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۹:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۹:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۲ ۲۰۲۳-۰۵-۰۱ ۱۴:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۱۴:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۱۴:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>پس از افزایش، اگر این را با تابع ROUND مقایسه کنیم، می بینیم که بسته به مقدار، ممکن است به بالا یا پایین گرد شود.select id,round(number_col) as number_col,round(date_col, ‘hh24’) as date_col,round(timestamp_col, ‘hh24’) as timestamp_col,round(interval_col, ‘hh24’) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱ ۲۰۲۳-۰۵-۰۱ ۰۸:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۸:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۸:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۲ ۲۰۲۳-۰۵-۰۱ ۱۴:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۱۴:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۱۴:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>FLOORتابع FLOOR مقادیر DATE، TIMESTAMP و INTERVAL را پایین می آورد. این شبیه به استفاده از تابع TRUNC با پارامتر قالب است. پارامتر فرمت پیش‌فرض روی «DD» است، بنابراین بدون پارامتر قالب، به ابتدای روز گرد می‌کنیم.select id,floor(number_col) as number_col,floor(date_col) as date_col,floor(timestamp_col) as timestamp_col,floor(interval_col) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۱ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>در اینجا ما معادل را با استفاده از تابع TRUNC می بینیم.select id,trunc(number_col) as number_col,trunc(date_col) as date_col,trunc(timestamp_col) as timestamp_col,trunc(interval_col) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۱ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۰:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۰:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>پارامتر فرمت سطح گرد شدن را تعیین می کند. در مثال زیر از «HH24» برای گرد کردن به ساعت قبل استفاده می‌کنیم.select id,floor(number_col) as number_col,floor(date_col, ‘hh24’) as date_col,floor(timestamp_col, ‘hh24’) as timestamp_col,floor(interval_col, ‘hh24’) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱ ۲۰۲۳-۰۵-۰۱ ۰۸:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۸:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۸:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۱ ۲۰۲۳-۰۵-۰۱ ۱۳:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۱۳:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۱۳:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>در اینجا ما معادل را با استفاده از تابع TRUNC می بینیم.select id,trunc(number_col) as number_col,trunc(date_col, ‘hh24’) as date_col,trunc(timestamp_col, ‘hh24’) as timestamp_col,trunc(interval_col, ‘hh24’) as interval_colfrom t1;ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL———- ———- ——————– ——————– ——————————۱ ۱ ۲۰۲۳-۰۵-۰۱ ۰۸:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۰۸:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۰۸:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰۲ ۱ ۲۰۲۳-۰۵-۰۱ ۱۳:۰۰:۰۰ ۲۰۲۳-۰۵-۰۱ ۱۳:۰۰:۰۰ +۰۰۰۰۰۰۰۰۰ ۱۳:۰۰:۰۰٫۰۰۰۰۰۰۰۰۰SQL>ارتباط با ماجهت دریافت خدمات مشاوره، آموزش و نگهداری پایگاه داده اوراکل با ما در ارتباط باشد

چگونه LOCK‌ها را در جداول PostgreSQL شناسایی و برطرف کنیم؟

مسعود سلطانی راد — Wed, 20 Nov 2024 08:16:35 +0330

گاهی اوقات هنگام اجرای یک کوئری در پایگاه داده، ممکن است با مشکل هنگ کردن کوئری مواجه شوید یا تراکنشی را باز کنید که هرگز پایان نمی‌یابد. چنین مواردی می‌توانند منجر به ایجاد LOCK روی جداول در PostgreSQL شوند. برای مدیریت این شرایط، باید LOCK‌ها و فرآیندهایی که آنها را ایجاد کرده‌اند شناسایی کرده و در صورت لزوم آنها را برطرف کنید.شناسایی LOCK‌هااولین نشانه وجود LOCK روی یک جدول، ناتوانی در اجرای عملیات ساده‌ای مانند حذف یک ردیف از جدول است. به‌عنوان مثال:design_system=> DELETE FROM search_hit WHERE id = 154193;Cancel request sentERROR: canceling statement due to user requestCONTEXT: while deleting tuple (22286,5) in relation “search_hit”مشاهده لیست LOCK‌هاPostgreSQL اطلاعات آماری جامعی در جداول متادیتا ذخیره می‌کند. یکی از این جداول pg_locks است. با پیوند این جدول به pg_class می‌توانید LOCK‌های موجود در جداول خاص را شناسایی کنید. برای مثال، جهت بررسی LOCK‌های روی جدول search_hit می‌توانید از کوئری زیر استفاده کنید:SELECT pidFROM pg_locks lJOIN pg_class t ON l.relation = t.oidWHERE t.relkind = ‘r’AND t.relname = ‘search_hit’;خروجی مشابه زیر خواهد بود:pid——-۱۱۳۳۷۱۶۳۸۹۱۶۳۸۹۱۱۹۲۹(۴ rows)این PIDها نشان‌دهنده فرآیندهایی هستند که منجر به LOCK شدن جدول شده‌اند.تطبیق کوئری با LOCK‌هابرای شناسایی کوئری‌هایی که LOCK‌ها را ایجاد کرده‌اند، می‌توانید از جدول pg_stat_activity استفاده کرده و اطلاعات مرتبط را فیلتر کنید:SELECT pid, state, usename, query, query_startFROM pg_stat_activityWHERE pid IN (SELECT pidFROM pg_locks lJOIN pg_class t ON l.relation = t.oidWHERE t.relkind = ‘r’AND t.relname = ‘search_hit’);این کوئری، اطلاعات مفصلی درباره فرآیندهای LOCK‌کننده، وضعیت آنها، نام کاربر، کوئری در حال اجرا و زمان شروع آن ارائه می‌دهد.برطرف کردن LOCK‌هااگر فرآیندهایی که LOCK‌ها را ایجاد کرده‌اند همچنان فعال باشند، می‌توانید با متوقف کردن یا خاتمه دادن به این فرآیندها، LOCK‌ها را برطرف کنید.روش ۱: لغو فرآیند با pg_cancel_backendبرای متوقف کردن یک فرآیند می‌توانید از pg_cancel_backend استفاده کنید. به‌عنوان مثال:SELECT pg_cancel_backend(11929);این دستور فرآیند مربوطه را متوقف می‌کند. با این حال، ممکن است این روش همیشه موفقیت‌آمیز نباشد. برای اطمینان، دوباره وضعیت LOCK‌ها را بررسی کنید.روش ۲: خاتمه فرآیند با pg_terminate_backendاگر فرآیند لغو نشود، می‌توانید با استفاده از pg_terminate_backend آن را به‌طور کامل خاتمه دهید:SELECT pg_terminate_backend(11929);پس از اجرای این دستور، می‌توانید با اجرای مجدد کوئری‌های بررسی LOCK اطمینان حاصل کنید که LOCK از بین رفته است. در صورت موفقیت، دیگر هیچ LOCKی روی جدول مشاهده نخواهید کرد.با استفاده از روش‌های فوق می‌توانید LOCK‌های جداول PostgreSQL را به‌سرعت شناسایی و مدیریت کنید تا از تأخیر در عملکرد پایگاه داده جلوگیری شود.

استفاده از تابع JSON_ARRAY با Subquery در پایگاه داده Oracle 23ai

مسعود سلطانی راد — Wed, 13 Nov 2024 09:04:24 +0330

از نسخه Oracle 23ai به بعد، تابع JSON_ARRAY قابلیت دریافت یک Subquery به عنوان ورودی را دارد. این به‌روزرسانی باعث تطابق بیشتر این تابع با استاندارد SQL/JSON شده و انعطاف‌پذیری بیشتری در کار با داده‌های JSON ارائه می‌دهد.تنظیمات اولیهبرای مثال‌های این مقاله، ابتدا دو جدول emp و dept را ایجاد می‌کنیم که داده‌های مربوط به بخش‌ها و کارکنان را ذخیره می‌کنند:drop table if exists emp purge;drop table if exists dept purge;create table dept (deptno number(2) constraint pk_dept primary key,dname varchar2(14),loc varchar2(13));create table emp (empno number(4) constraint pk_emp primary key,ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2) constraint fk_deptno references dept);با استفاده از دستورات زیر، داده‌های نمونه‌ای را به جداول اضافه می‌کنیم:insert into dept values (10,’ACCOUNTING’,’NEW YORK’);insert into dept values (20,’RESEARCH’,’DALLAS’);insert into dept values (30,’SALES’,’CHICAGO’);insert into dept values (40,’OPERATIONS’,’BOSTON’);insert into emp values (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,null,20);— سایر داده‌های کارکنان نیز در ادامه به جدول اضافه شده‌اندcommit;بررسی JSON_ARRAY در نسخه‌های قبلی Oracleتابع JSON_ARRAY برای اولین بار در Oracle 12.1 معرفی شد. این تابع امکان تبدیل لیستی از مقادیر به فرمت JSON را فراهم می‌کند:select json_array(empno, ename)from empwhere deptno = 10;خروجی:[۷۷۸۲,”CLARK”][۷۸۳۹,”KING”][۷۹۳۴,”MILLER”]محدودیت‌های اولیه و راه‌حلدر نسخه‌های اولیه، امکان استفاده از Subquery در تابع JSON_ARRAY وجود نداشت، که کار با داده‌های پیچیده‌تر را دشوار می‌کرد. برای مثال، برای ایجاد سند JSON یک بخش به همراه کارکنان، مجبور به استفاده از JSON_ARRAYAGG بودیم:select json_serialize(json_object(‘department_number’ : d.deptno,‘department_name’ : d.dname,’employees’ : (select json_arrayagg(json_object(’employee_number’ : e.empno,’employee_name’ : e.ename))from emp ewhere e.deptno = d.deptno))pretty) as outputfrom dept dwhere d.deptno = 10;خروجی:{“department_number” : 10,“department_name” : “ACCOUNTING”,“employees” :[{“employee_number” : 7782,“employee_name” : “CLARK”},{“employee_number” : 7839,“employee_name” : “KING”},{“employee_number” : 7934,“employee_name” : “MILLER”}]}قابلیت‌های جدید در Oracle 23aiدر Oracle 23ai، امکان استفاده مستقیم از Subquery در JSON_ARRAY اضافه شده است. این ویژگی، فرآیند ایجاد JSON شامل داده‌های تو در تو را ساده‌تر کرده است. اکنون می‌توانیم نمونه قبلی را به صورت زیر بازنویسی کنیم:select json_serialize(json_object(‘department-number’ : d.deptno,‘department-name’ : d.dname,’employees’ : json_array(select json_object(’employee-number’ : e.empno,’employee-name’ : e.ename)from emp ewhere e.deptno = d.deptno))pretty) as outputfrom dept dwhere d.deptno = 10;خروجی:jsonCopy code{“department-number” : 10,“department-name” : “ACCOUNTING”,“employees” :[{“employee-number” : 7782,“employee-name” : “CLARK”},{“employee-number” : 7839,“employee-name” : “KING”},{“employee-number” : 7934,“employee-name” : “MILLER”}]}نتیجه‌گیریاستفاده از تابع JSON_ARRAY با Subquery در Oracle 23ai امکان کار با داده‌های JSON پیچیده را به‌طور بهینه‌تری فراهم کرده است. این قابلیت به توسعه‌دهندگان کمک می‌کند تا با کاهش پیچیدگی کد، داده‌های JSON مورد نیاز خود را به راحتی و به صورت استاندارد تولید کنند.

نحوه زمان‌بندی کارها در PostgreSQL با استفاده از pgAgent و Cron Jobs

مسعود سلطانی راد — Wed, 13 Nov 2024 08:59:01 +0330

زمان‌بندی و خودکارسازی وظایف یکی از جنبه‌های کلیدی مدیریت پایگاه داده PostgreSQL است. ابزار pgAgent و Cron هر دو گزینه‌هایی برای زمان‌بندی وظایف در PostgreSQL هستند که هر یک مزایا و کاربردهای خاص خود را دارند.زمان‌بندی کارها با pgAgentیکی از ابزارهای مفید برای زمان‌بندی وظایف در PostgreSQL، pgAgent است. این ابزار مخصوص PostgreSQL طراحی شده و به کاربران اجازه می‌دهد وظایفی همچون پشتیبان‌گیری، بارگذاری داده‌ها، و تولید گزارش‌ها را زمان‌بندی و خودکار کنند.مراحل نصب و پیکربندی pgAgentابتدا لازم است pgAgent را با پایگاه داده PostgreSQL نصب و پیکربندی کنید. پس از نصب، می‌توانید وظایف را با دستورات SQL، رویه‌های ذخیره شده، یا اسکریپت‌های پوسته ایجاد کنید. سپس می‌توانید این وظایف را برای اجرا در زمان‌های مشخص زمان‌بندی کنید.برای نصب pgAgent از دستور زیر استفاده کنید:CREATE EXTENSION pgagent;سپس شمای pgAgent را به این صورت ایجاد کنید:CREATE SCHEMA pgagent;برای مقداردهی اولیه جداول pgAgent، فایل SQL مربوطه که معمولاً در مسیر share/pgagent.sql قرار دارد را اجرا کنید:\i /path/to/share/pgagent.sqlایجاد و زمان‌بندی وظایفپس از راه‌اندازی pgAgent، می‌توانید وظایف جدیدی برای اجرای دستورات SQL ایجاد کنید. به عنوان مثال:BEGIN;SELECT pgagent.pga_jobid(‘MyJob’) INTO my_job_id;SELECT pgagent.pga_job(pjobid := my_job_id,pscheduleid := NULL,pjobname := ‘MyJob’,pjobdesc := ‘My job description’,pjobhostagent := ”,pjobenabled := TRUE,pjobhostagentislocal := TRUE,pjobnextrun := now(),pjobcode := ‘SELECT COUNT(*) FROM my_table;’,pjobconnstr := ‘host=localhost dbname=my_database user=my_user password=my_password’);COMMIT;در این کد، نام، توضیحات، و کد SQL وظیفه را متناسب با نیاز خود تنظیم کنید.برای زمان‌بندی یک وظیفه، به این شکل عمل کنید:BEGIN;SELECT pgagent.pga_schedule(pschedid := NULL,pschedname := ‘MyJobSchedule’,pscheddesc := ‘My job schedule description’,pschedenabled := TRUE,pschedstart := now(),pschedend := NULL,pschedminutes := ‘0,15,30,45’,pschedhours := ‘*’,pschedweekdays := ‘*’,pschedmonthdays := ‘*’,pschedmonths := ‘*’,pschedord := NULL,pschedweek := NULL,pschedday := NULL,pjobid := my_job_id);COMMIT;این برنامه وظیفه را هر ۱۵ دقیقه اجرا می‌کند. زمان‌بندی و نام‌ها را مطابق نیازهای خود تنظیم کنید. کاربران می‌توانند از pgAdmin برای نظارت بر اجرای وظایف استفاده کنند.زمان‌بندی کارها با Cron Jobs در سیستم‌عامل‌های یونیکسراه‌حل دیگری برای زمان‌بندی وظایف در PostgreSQL، استفاده از Cron در سیستم‌های یونیکسی مانند لینوکس است. Cron به کاربران اجازه می‌دهد تا کارها را به صورت تکراری خودکارسازی کنند.تنظیم Cron Job برای PostgreSQLبرای زمان‌بندی کارها، می‌توانید یک اسکریپت پوسته‌ای بنویسید که دستورات SQL مورد نظر را با psql یا ابزارهای دیگر اجرا کند. پس از آن، با استفاده از دستور cron، کار جدید را به جدول cron اضافه کنید.همچنین می‌توانید از پسوند pg_cron استفاده کنید که به شما اجازه می‌دهد مستقیماً از طریق PostgreSQL زمان‌بندی را انجام دهید:SELECT cron.schedule(‘* * * * *’, ‘SELECT my_function();’);این مثال، my_function را بر اساس زمان‌بندی مشخص اجرا می‌کند.برای حذف یک وظیفه زمان‌بندی شده:SELECT cron.unschedule(jobid);دقت کنید که زمان‌بندی کارها با pg_cron به امتیازات superuser نیاز دارد.با استفاده از pgAgent و Cron می‌توانید عملیات پایگاه داده PostgreSQL را به آسانی زمان‌بندی و خودکار کنید و کارایی و انعطاف‌پذیری بیشتری در مدیریت پایگاه داده داشته باشید.

عملگرهای کیفیت داده FUZZY_MATCH و PHONIC_ENCODE در اوراکل ۲۳ai

مسعود سلطانی راد — Wed, 30 Oct 2024 07:19:03 +0330

پایگاه داده Oracle 23ai عملگرهای کیفیت داده FUZZY_MATCH و PHONIC_ENCODE را برای انجام تطبیق رشته فازی معرفی کرد.بسته UTL_MATCH در Oracle 10g Release 2 معرفی شد، اما برای اولین بار در Oracle 11g Release 2 مستند شد (و بنابراین پشتیبانی شد). این شامل انواع توابع است که برای آزمایش سطح شباهت/تفاوت بین رشته ها مفید است. عملگرهای FUZZY_MATCH و PHONIC_ENCODE عملکرد تطبیق رشته فازی پایگاه داده را گسترش می دهند.آشنایی با قابلیت های پایگاه داده Oracle 23aiراه اندازیمثال های این مقاله به جدول تست زیر نیاز دارند.drop table if exists match_tab;create table match_tab (id number,col1 varchar2(15),col2 varchar2(15),constraint match_tab_pk primary key (id));insert into match_tab values(۱, ‘Peter Parker’, ‘Pete Parker’),(۲, ‘Peter Parker’, ‘peter parker’),(۳, ‘Clark Kent’, ‘Claire Kent’),(۴, ‘Wonder Woman’, ‘Ponder Woman’),(۵, ‘Superman’, ‘Superman’),(۶, ‘The Hulk’, ‘Iron Man’);commit;توجه داشته باشید که مقادیر COL1 و COL2 درجات مختلفی از شباهت از جمله مطابقت دقیق، مطابقت نزدیک و مقادیر کاملاً متفاوت دارند.FUZZY_MATCHعملگر FUZZY_MATCH از نظر زبان خنثی است. شباهت بین دو رشته را تعیین می کند و از چندین الگوریتم لیست شده در اینجا پشتیبانی می کند.set linesize 100column col1 format a12column col2 format a12select col1,col2,fuzzy_match(levenshtein, col1, col2) as levenshtein,fuzzy_match(jaro_winkler, col1, col2) as jaro_winkler,fuzzy_match(bigram, col1, col2) as bigram,fuzzy_match(trigram, col1, col2) as trigram,fuzzy_match(whole_word_match, col1, col2) as wwm,fuzzy_match(longest_common_substring, col1, col2) as lcsfrom match_tab;COL1 COL2 LEVENSHTEIN JARO_WINKLER BIGRAM TRIGRAM WWM LCS———— ———— ———– ———— ———- ———- ———- ———-Peter Parker Pete Parker ۹۲ ۹۲ ۹۰ ۷۰ ۵۰ ۵۸Peter Parker peter parker ۸۴ ۸۸ ۷۲ ۶۰ ۰ ۴۱Clark Kent Claire Kent ۸۲ ۹۰ ۶۰ ۴۴ ۵۰ ۴۵Wonder Woman Ponder Woman ۹۲ ۹۴ ۱۰۰ ۹۰ ۵۰ ۹۱Superman Superman ۱۰۰ ۱۰۰ ۱۰۰ ۱۰۰ ۱۰۰ ۱۰۰The Hulk Iron Man ۰ ۴۱ ۰ ۰ ۰ ۱۲۶ rows selected.SQL>به طور پیش فرض خروجی یک درصد شباهت است، اما کلمه کلیدی UNSCALED را می توان برای برگرداندن مقدار خام اضافه کرد.select col1,col2,fuzzy_match(levenshtein, col1, col2, unscaled) as levenshtein,fuzzy_match(jaro_winkler, col1, col2, unscaled) as jaro_winkler,fuzzy_match(bigram, col1, col2, unscaled) as bigram,fuzzy_match(trigram, col1, col2, unscaled) as trigram,fuzzy_match(whole_word_match, col1, col2, unscaled) as wwm,fuzzy_match(longest_common_substring, col1, col2, unscaled) as lcsfrom match_tab;COL1 COL2 LEVENSHTEIN JARO_WINKLER BIGRAM TRIGRAM WWM LCS———— ———— ———– ———— ———- ———- ———- ———-Peter Parker Pete Parker ۱ .۹۲ ۱۰ ۷ ۱ ۷Peter Parker peter parker ۲ .۸۸ ۸ ۶ ۰ ۵Clark Kent Claire Kent ۲ .۹ ۶ ۴ ۱ ۵Wonder Woman Ponder Woman ۱ .۹۴ ۱۱ ۹ ۱ ۱۱Superman Superman ۰ ۱ ۷ ۶ ۱ ۸The Hulk Iron Man ۸ .۴۱ ۰ ۰ ۰ ۱۶ rows selected.SQL>اگر از بسته UTL_MATCH استفاده کرده‌اید، مقادیر مقیاس‌بندی‌شده بدون مقیاس، فراخوان‌های زیر را نشان می‌دهند.UnscaledScaledAlgorithmUTL_MATCH.EDIT_DISTANCEUTL_MATCH.EDIT_DISTANCE_SIMILARITYLEVENSHTEINUTL_MATCH.JARO_WINKLERUTL_MATCH.JARO_WINKLER_SIMILARITYJARO_WINKLERبه‌طور پیش‌فرض، خروجی با طول رشته ورودی طولانی‌تر مقیاس‌بندی می‌شود. کلمه کلیدی RELATE_TO_SHORTER این را تغییر می دهد تا به رشته ورودی کوتاه تر تبدیل شود.select col1,col2,fuzzy_match(levenshtein, col1, col2, relate_to_shorter) as levenshtein,fuzzy_match(jaro_winkler, col1, col2, relate_to_shorter) as jaro_winkler,fuzzy_match(bigram, col1, col2, relate_to_shorter) as bigram,fuzzy_match(trigram, col1, col2, relate_to_shorter) as trigram,fuzzy_match(whole_word_match, col1, col2, relate_to_shorter) as wwm,fuzzy_match(longest_common_substring, col1, col2, relate_to_shorter) as lcsfrom match_tab;COL1 COL2 LEVENSHTEIN JARO_WINKLER BIGRAM TRIGRAM WWM LCS———— ———— ———– ———— ———- ———- ———- ———-Peter Parker Pete Parker ۹۱ ۹۲ ۱۰۰ ۷۷ ۵۰ ۶۳Peter Parker peter parker ۸۴ ۸۸ ۷۲ ۶۰ ۰ ۴۱Clark Kent Claire Kent ۸۰ ۹۰ ۶۶ ۵۰ ۵۰ ۵۰Wonder Woman Ponder Woman ۹۲ ۹۴ ۱۰۰ ۹۰ ۵۰ ۹۱Superman Superman ۱۰۰ ۱۰۰ ۱۰۰ ۱۰۰ ۱۰۰ ۱۰۰The Hulk Iron Man ۰ ۴۱ ۰ ۰ ۰ ۱۲۶ rows selected.SQL>کلمه کلیدی EDIT_TOLERANCE را می توان با الگوریتم WHOLE_WORD_MATCH استفاده کرد. تلورانس درصدی از کاراکترهای یک کلمه است که می تواند متفاوت باشد، در حالی که هنوز آن را همان کلمه در نظر می گیریم.select col1,col2,fuzzy_match(whole_word_match, col1, col2) as wwm,fuzzy_match(whole_word_match, col1, col2, edit_tolerance 20) as wwm20,fuzzy_match(whole_word_match, col1, col2, edit_tolerance 82) as wwm82from match_tab;COL1 COL2 WWM WWM20 WWM82———— ———— ———- ———- ———-Peter Parker Pete Parker ۵۰ ۱۰۰ ۵۰Peter Parker peter parker ۰ ۱۰۰ ۵۰Clark Kent Claire Kent ۵۰ ۱۰۰ ۵۰Wonder Woman Ponder Woman ۵۰ ۱۰۰ ۱۰۰Superman Superman ۱۰۰ ۱۰۰ ۱۰۰The Hulk Iron Man ۰ ۰ ۰۶ rows selected.SQL>PHONIC_ENCODEعملگر PHONIC_ENCODE متن را بر اساس تلفظ متن به کدهای خاص زبان تبدیل می کند. این الگوریتم Double Metaphone و یک الگوریتم جایگزین را پیاده سازی می کند.set linesize 100column col1 format a12column col2 format a12column col1_dm format a8column col2_dm format a8column col1_dma format a8column col2_dma format a8select col1,col2,phonic_encode(double_metaphone, col1) as col1_dm,phonic_encode(double_metaphone, col2) as col2_dm,phonic_encode(double_metaphone_alt, col1) as col1_dma,phonic_encode(double_metaphone_alt, col2) as col2_dmafrom match_tab;COL1 COL2 COL1_DM COL2_DM COL1_DMA COL2_DMA———— ———— ——– ——– ——– ——–Peter Parker Pete Parker PTRP PTPR PTRP PTPRPeter Parker peter parker PTRP PTRP PTRP PTRPClark Kent Claire Kent KLRK KLRK KLRK KLRKWonder Woman Ponder Woman ANTR PNTR FNTR PNTRSuperman Superman SPRM SPRM SPRM SPRMThe Hulk Iron Man ۰LK ARNM TLK ARNM۶ rows selected.SQL>هنگام استفاده از DOUBLE_METAPHONE_ALT، اگر کد دیگری وجود نداشته باشد، کد اصلی برگردانده می شود.حداکثر طول کد توسط یک پارامتر سوم اختیاری کنترل می شود که مقادیر صحیح از ۱ تا ۱۲ را می پذیرد.column col1_dm1 format a9column col2_dm1 format a9column col1_dm6 format a9column col2_dm6 format a9column col1_dm12 format a9column col2_dm12 format a9select col1,col2,phonic_encode(double_metaphone, col1, 1) as col1_dm1,phonic_encode(double_metaphone, col2, 1) as col2_dm1,phonic_encode(double_metaphone, col1, 6) as col1_dm6,phonic_encode(double_metaphone, col2, 6) as col2_dm6,phonic_encode(double_metaphone, col1, 12) as col1_dm12,phonic_encode(double_metaphone, col2, 12) as col2_dm12from match_tab;COL1 COL2 COL1_DM1 COL2_DM1 COL1_DM6 COL2_DM6 COL1_DM12 COL2_DM12———— ———— ——— ——— ——— ——— ——— ———Peter Parker Pete Parker P P PTRPRK PTPRKR PTRPRKR PTPRKRPeter Parker peter parker P P PTRPRK PTRPRK PTRPRKR PTRPRKRClark Kent Claire Kent K K KLRKKN KLRKNT KLRKKNT KLRKNTWonder Woman Ponder Woman A P ANTRMN PNTRMN ANTRMN PNTRMNSuperman Superman S S SPRMN SPRMN SPRMN SPRMNThe Hulk Iron Man ۰ A ۰LK ARNMN ۰LK ARNMN۶ rows selected.SQL>پشتیبانی PL/SQLدر این نسخه هیچ پشتیبانی مستقیمی برای عملگرهای FUZZY_MATCH یا PHONIC_ENCODE در PL/SQL وجود ندارد، بنابراین تخصیص مستقیم امکان پذیر نیست.declarel_output number;beginl_output := fuzzy_match(levenshtein, ‘Peter Parker’, ‘peter parker’);end;/*ERROR at line 4:ORA-06550: line 4, column 15:PLS-00201: identifier ‘FUZZY_MATCH’ must be declaredORA-06550: line 4, column 3:PL/SQL: Statement ignoredSQL>declarel_output varchar2(10);beginl_output := phonic_encode(double_metaphone, ‘Peter Parker’);end;/*ERROR at line 4:ORA-06550: line 4, column 15:PLS-00201: identifier ‘PHONIC_ENCODE’ must be declaredORA-06550: line 4, column 3:PL/SQL: Statement ignoredSQL>ما می توانیم از یک SELECT … INTO برای انجام انتساب استفاده کنیم.declarel_output number;beginselect fuzzy_match(levenshtein, ‘Peter Parker’, ‘peter parker’)into l_output;end;/PL/SQL procedure successfully completed.SQL>declarel_output varchar2(10);beginselect phonic_encode(double_metaphone, ‘Peter Parker’)into l_output;end;/PL/SQL procedure successfully completed.SQL>

PostgreSQL مدیریت Table Fragmentation

مسعود سلطانی راد — Tue, 22 Oct 2024 11:32:21 +0330

PostgreSQL یک سیستم پایگاه داده قدرتمند و قابل اعتماد است که ویژگی‌ها و مزایای زیادی ارائه می‌دهد. با این حال، برخی از چالش‌ها و معایب نیز وجود دارد، مانند مشکل table fragmentation. Table fragmentation زمانی رخ می‌دهد که فضای اشغال‌شده توسط یک جدول یا شاخص بزرگ‌تر از اندازه واقعی داده‌ها باشد. این موضوع می‌تواند بر عملکرد و کارایی پایگاه داده تأثیر منفی بگذارد و همچنین زمان پشتیبان‌گیری و بازیابی را افزایش دهد.یکی از دلایل اصلی اجرای vacuum بر روی جداول، تجمع dead tuples است. Dead tuples به ردیف‌هایی اطلاق می‌شود که حذف یا به‌روزرسانی شده‌اند، اما همچنان برای تراکنش‌های همزمان قابل مشاهده هستند. PostgreSQL از مکانیزمی به نام MVCC (Multi-Version Concurrency Control) برای اطمینان از جداسازی و سازگاری تراکنش‌ها استفاده می‌کند. این مکانیزم به هر تراکنش اجازه می‌دهد بدون قفل کردن ردیف‌ها یا جداول، وضعیت پایگاه داده را در یک نقطه زمانی خاص مشاهده کند. این بدان معناست که نسخه‌های قدیمی ردیف‌ها بلافاصله حذف نمی‌شوند، بلکه به‌عنوان dead tuples علامت‌گذاری می‌شوند و برای تراکنش‌های جدید غیرقابل مشاهده می‌گردند.برای بازیابی فضای اشغال‌شده توسط dead tuples، PostgreSQL فرآیندی به نام vacuum را اجرا می‌کند. Vacuum این dead tuples را از جداول و شاخص‌ها حذف می‌کند و فضای آزادشده را برای استفاده مجدد آماده می‌نماید. Vacuum همچنین نقشه دید (visibility map) را به‌روزرسانی می‌کند که نشان می‌دهد کدام صفحات یک جدول شامل ردیف‌های قابل مشاهده هستند. این به تسریع index-only scans کمک می‌کند، زیرا اگر نقشه دید نشان دهد که نیازی به خواندن صفحات جدول نیست، از خواندن آنها جلوگیری می‌شود.AutovacuumPostgreSQL دارای قابلیتی به نام autovacuum است که به‌صورت خودکار در پس‌زمینه اجرا می‌شود و بر اساس نیاز جداولی که به vacuum نیاز دارند، عمل می‌کند. Autovacuum بر اساس عواملی مانند تعداد dead tuples، سن شناسه تراکنش و اندازه جدول فعال می‌شود. علاوه بر vacuum، autovacuum فرآیند analyze را نیز انجام می‌دهد که آمار جداول را برای به‌روزرسانی برنامه‌ریز پرس‌وجو و انتخاب بهترین طرح اجرا به‌روز می‌کند.با این حال، در برخی موارد ممکن است autovacuum به‌موقع اجرا نشود، به‌ویژه در جداولی که نرخ به‌روزرسانی یا حذف بالایی دارند. این امر می‌تواند منجر به تجمع بیش از حد table fragmentation و عملکرد ضعیف پرس‌وجوها شود. در این مقاله، نحوه کنترل فرکانس اجرای autovacuum برای جداول خاص را با استفاده از پارامترهای ذخیره‌سازی بررسی خواهیم کرد.پارامترهای ذخیره‌سازیStorage parameters تنظیماتی هستند که می‌توان برای هر جدول یا شاخص تعیین کرد تا ویژگی‌های فیزیکی ذخیره‌سازی آن‌ها را کنترل کرد. برخی از این پارامترها مستقیماً بر نحوه اجرای autovacuum تأثیر می‌گذارند. دو پارامتری که در اینجا به آنها می‌پردازیم عبارتند از:autovacuum_vacuum_threshold: حداقل تعداد dead tuples در یک جدول قبل از اینکه autovacuum دستور VACUUM را اجرا کند. مقدار پیش‌فرض آن ۵۰ است.autovacuum_vacuum_scale_factor: کسری از تعداد کل ردیف‌های یک جدول که به autovacuum_vacuum_threshold اضافه می‌شود تا تصمیم‌گیری شود که آیا autovacuum باید دستور VACUUM را اجرا کند یا خیر. مقدار پیش‌فرض ۰٫۲ است.فرمول اجرای autovacuum به این صورت است:(تعداد dead tuples) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (تعداد کل ردیف‌ها)به‌عنوان مثال، اگر یک جدول ۱۰۰۰۰۰ ردیف و ۲۰۰۰۰ dead tuples داشته باشد، autovacuum زمانی اجرا می‌شود که:۲۰۰۰۰ > 50 + 0.2 * 100000که ساده می‌شود به:۲۰۰۰۰ > 20050ازآنجاکه این شرط نادرست است، autovacuum اجرا نخواهد شد.اما اگر پارامترهای ذخیره‌سازی این جدول را به این شکل تغییر دهیم:autovacuum_vacuum_threshold = 1000autovacuum_vacuum_scale_factor = 0حال autovacuum زمانی اجرا می‌شود که:۲۰۰۰۰ > 1000 + 0 * 100000که ساده می‌شود به:۲۰۰۰۰ > 1000و ازآنجاکه این شرط درست است، autovacuum اجرا خواهد شد.با تنظیم این پارامترها، می‌توانیم بر اساس نیاز خود رفتار autovacuum را تهاجمی‌تر یا محافظه‌کارانه‌تر کنیم.مثالبرای مثال، از جدول pgbench_accounts از ابزار pgbench استفاده می‌کنیم. این جدول شامل ۱۰۰۰۰۰ ردیف است و به‌طور مرتب توسط تراکنش‌ها به‌روزرسانی می‌شود.می‌توانیم پارامترهای ذخیره‌سازی فعلی این جدول را با دستور \d+ در psql بررسی کنیم:postgres=# \d+ pgbench_accountsنتیجه:Table “public.pgbench_accounts”Column | Type | Collation | Nullable | Default | Storage | Description———+————+———–+———-+———+———-+————-aid | integer | | not null | | plain |bid | integer | | | | plain |abalance| integer | | | | plain |filler | char(84) | | | | extended |Indexes:“pgbench_accounts_pkey” PRIMARY KEY, btree (aid)“pgbench_accounts_bid_index” btree (bid)Options: autovacuum_vacuum_threshold=1000, autovacuum_vacuum_scale_factor=0این جدول دارای پارامترهای ذخیره‌سازی است که مدنظر ما است. اگر بخواهیم آنها را تغییر دهیم، می‌توانیم از دستور ALTER TABLE استفاده کنیم:postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000);ALTER TABLEpostgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.0);ALTER TABLEحال، چندین ردیف از این جدول را به‌روزرسانی می‌کنیم تا dead tuples ایجاد کنیم:postgres=# update pgbench_accounts set abalance = 1 where aid < 1002;UPDATE 1001می‌توانیم تعداد dead tuples و آخرین زمان اجرای autovacuum را از نمای pg_stat_all_tables بررسی کنیم:postgres=# SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_all_tables WHERE relname = ‘pgbench_accounts’;relname | n_dead_tup | last_autovacuum——————+————+——————————-pgbench_accounts | ۱۰۰۱ | ۲۰۲۳-۰۷-۰۱ ۰۰:۳۲:۲۳٫۶۲۷۹۳۱+۰۹پس از چند دقیقه، می‌بینیم که autovacuum اجرا می‌شود:postgres=# SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_all_tables WHERE relname = ‘pgbench_accounts’;relname | n_dead_tup | last_autovacuum——————+————+——————————-pgbench_accounts | ۰ | ۲۰۲۳-۰۷-۰۱ ۰۰:۴۳:۲۴٫۴۹۸۵۹۸+۰۹همان‌طور که مشاهده می‌شود، تعداد dead tuples به صفر رسیده و autovacuum به‌موقع اجرا شده است. این نشان می‌دهد که تنظیمات autovacuum به‌درستی عمل کرده است.

بررسی Graphql در پایگاه داده Oracle 23ai

مسعود سلطانی راد — Wed, 16 Oct 2024 10:33:57 +0330

graphql چیست؟“GraphQL یک پرس و جو و دستکاری داده برای API است ، که به client امکان می دهد داده های مورد نیاز خود را تعیین کند (” داده های اجرای داده “). یک سرور GraphQL می تواند یک منبع جداگانه برای یک پرس و جو ایجاد کند و نتایج را در یک نمودار یکپارچه ارائه دهد ، بنابراین به هر پایگاه داده یا موتور ذخیره سازی گره خورده است. ”آشنایی با قابلیت های پایگاه داده Oracle 23aiراه اندازیما برخی از جدول ها را برای کار با آنها ایجاد و جمع می کنیم.drop table if exists emp purge;drop table if exists dept purge;create table dept (deptno number(2) constraint pk_dept primary key,dname varchar2(14),loc varchar2(13)) ;create table emp (empno number(4) constraint pk_emp primary key,ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2) constraint fk_deptno references dept);create index emp_dept_fk_i on emp(deptno);insert into dept values (10,’ACCOUNTING’,’NEW YORK’);insert into dept values (20,’RESEARCH’,’DALLAS’);insert into dept values (30,’SALES’,’CHICAGO’);insert into dept values (40,’OPERATIONS’,’BOSTON’);insert into emp values (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,null,20);insert into emp values (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);insert into emp values (7521,’WARD’,’SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);insert into emp values (7566,’JONES’,’MANAGER’,7839,to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,null,20);insert into emp values (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);insert into emp values (7698,’BLAKE’,’MANAGER’,7839,to_date(‘1-5-1981′,’dd-mm-yyyy’),2850,null,30);insert into emp values (7782,’CLARK’,’MANAGER’,7839,to_date(‘9-6-1981′,’dd-mm-yyyy’),2450,null,10);insert into emp values (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,’dd-mm-rr’)-85,3000,null,20);insert into emp values (7839,’KING’,’PRESIDENT’,null,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,null,10);insert into emp values (7844,’TURNER’,’SALESMAN’,7698,to_date(‘8-9-1981′,’dd-mm-yyyy’),1500,0,30);insert into emp values (7876,’ADAMS’,’CLERK’,7788,to_date(’13-JUL-87′, ‘dd-mm-rr’)-51,1100,null,20);insert into emp values (7900,’JAMES’,’CLERK’,7698,to_date(‘3-12-1981′,’dd-mm-yyyy’),950,null,30);insert into emp values (7902,’FORD’,’ANALYST’,7566,to_date(‘3-12-1981′,’dd-mm-yyyy’),3000,null,20);insert into emp values (7934,’MILLER’,’CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,null,10);commit;عملکرد جدول GraphQLعملکرد جدول GraphQL به ما امکان می دهد تا از داده های رابطه ای با استفاده از نحو GraphQL استفاده کنیم.در مثال زیر جدول DEPT را پرس و جو می کنیم و خروجی مورد نیاز را با استفاده از GraphQL تعریف می کنیم. GraphQL ساختار سند JSON را که بازگردانده خواهد شد ، نشان می دهد و ستون هایی را که هر عنصر را ارائه می دهد ، ارجاع می دهد.select * from graphql (‘department : dept{departmentNumber: deptnodepartmentName : dnamelocation : loc}’);JSON_OBJECT(‘DEPARTMENTNUMBER’VALUE”DEPTNO”,’DEPARTMENTNAME’VALUE”DNAME”,’LOCATI——————————————————————————–{“departmentNumber”:10,”departmentName”:”ACCOUNTING”,”location”:”NEW YORK”}{“departmentNumber”:20,”departmentName”:”RESEARCH”,”location”:”DALLAS”}{“departmentNumber”:30,”departmentName”:”SALES”,”location”:”CHICAGO”}{“departmentNumber”:40,”departmentName”:”OPERATIONS”,”location”:”BOSTON”}SQL>ما می توانیم کاری مشابه با جدول EMP انجام دهیم.select * from graphql(‘employees : emp{employeeNumber : empnoemployeeName : enamejob : jobsalary : salhireDate : hiredate}’);JSON_OBJECT(‘EMPLOYEENUMBER’VALUE”EMPNO”,’EMPLOYEENAME’VALUE”ENAME”,’JOB’VALUE”JOB”,’SALARY’VALUE”SAL”,’HIREDATE’VALUE”HIREDATE”NULLONNULLEMPTYONNOROWSRETURNINGJSONETAG)——————————————————————————————————————————————————————————————————–{“employeeNumber”:7369,”employeeName”:”SMITH”,”job”:”CLERK”,”salary”:800,”hireDate”:”1980-12-17T00:00:00″}{“employeeNumber”:7499,”employeeName”:”ALLEN”,”job”:”SALESMAN”,”salary”:1600,”hireDate”:”1981-02-20T00:00:00″}{“employeeNumber”:7521,”employeeName”:”WARD”,”job”:”SALESMAN”,”salary”:1250,”hireDate”:”1981-02-22T00:00:00″}{“employeeNumber”:7566,”employeeName”:”JONES”,”job”:”MANAGER”,”salary”:2975,”hireDate”:”1981-04-02T00:00:00″}{“employeeNumber”:7654,”employeeName”:”MARTIN”,”job”:”SALESMAN”,”salary”:1250,”hireDate”:”1981-09-28T00:00:00″}{“employeeNumber”:7698,”employeeName”:”BLAKE”,”job”:”MANAGER”,”salary”:2850,”hireDate”:”1981-05-01T00:00:00″}{“employeeNumber”:7782,”employeeName”:”CLARK”,”job”:”MANAGER”,”salary”:2450,”hireDate”:”1981-06-09T00:00:00″}{“employeeNumber”:7788,”employeeName”:”SCOTT”,”job”:”ANALYST”,”salary”:3000,”hireDate”:”1987-04-19T00:00:00″}{“employeeNumber”:7839,”employeeName”:”KING”,”job”:”PRESIDENT”,”salary”:5000,”hireDate”:”1981-11-17T00:00:00″}{“employeeNumber”:7844,”employeeName”:”TURNER”,”job”:”SALESMAN”,”salary”:1500,”hireDate”:”1981-09-08T00:00:00″}{“employeeNumber”:7876,”employeeName”:”ADAMS”,”job”:”CLERK”,”salary”:1100,”hireDate”:”1987-05-23T00:00:00″}{“employeeNumber”:7900,”employeeName”:”JAMES”,”job”:”CLERK”,”salary”:950,”hireDate”:”1981-12-03T00:00:00″}{“employeeNumber”:7902,”employeeName”:”FORD”,”job”:”ANALYST”,”salary”:3000,”hireDate”:”1981-12-03T00:00:00″}{“employeeNumber”:7934,”employeeName”:”MILLER”,”job”:”CLERK”,”salary”:1300,”hireDate”:”1982-01-23T00:00:00″}۱۴ rows selected.SQL>۱در مثال زیر ما یک سند JSON حاوی اطلاعات بخش را برمی گردانیم ، که شامل اطلاعات مربوط به کارمندان آن بخش است.select * from graphql(‘department : dept{departmentNumber: deptnodepartmentName : dnamelocation : locemployees : emp[{employeeNumber : empnoemployeeName : enamejob : jobsalary : sal}]}’);JSON_OBJECT(‘DEPARTMENTNUMBER’VALUE”DEPTNO”,’DEPARTMENTNAME’VALUE”DNAME”,’LOCATION’VALUE”LOC”,’EMPLOYEES’VALUE(SELECTJSON_ARRAYAGG(JSON_OBJECT(‘EMPLOYEENUMBER’VALUE”EMPNO”,’EMPLOYEENAME’VALUE”ENAME”,——————————————————————————————————————————————————————————————————–{“departmentNumber”:10,”departmentName”:”ACCOUNTING”,”location”:”NEW YORK”,”employees”:[{“employeeNumber”:7782,”employeeName”:”CLARK”,”job”:”MANAGER”,”salary”:2450},{“employeeNumber”:7839,”employeeName”:”KING”,”job”:”PRESIDENT”,”salary”:5000},{“employeeNumber”:7934,”employeeName”:”MILLER”,”job”:”CLERK”,”salary”:1300}]}{“departmentNumber”:20,”departmentName”:”RESEARCH”,”location”:”DALLAS”,”employees”:[{“employeeNumber”:7369,”employeeName”:”SMITH”,”job”:”CLERK”,”salary”:800},{“employeeNumber”:7566,”employeeName”:”JONES”,”job”:”MANAGER”,”salary”:2975},{“employeeNumber”:7788,”employeeName”:”SCOTT”,”job”:”ANALYST”,”salary”:3000},{“employeeNumber”:7876,”employeeName”:”ADAMS”,”job”:”CLERK”,”salary”:1100},{“employeeNumber”:7902,”employeeName”:”FORD”,”job”:”ANALYST”,”salary”:3000}]}{“departmentNumber”:30,”departmentName”:”SALES”,”location”:”CHICAGO”,”employees”:[{“employeeNumber”:7499,”employeeName”:”ALLEN”,”job”:”SALESMAN”,”salary”:1600},{“employeeNumber”:7521,”employeeName”:”WARD”,”job”:”SALESMAN”,”salary”:1250},{“employeeNumber”:7654,”employeeName”:”MARTIN”,”job”:”SALESMAN”,”salary”:1250},{“employeeNumber”:7698,”employeeName”:”BLAKE”,”job”:”MANAGER”,”salary”:2850},{“employeeNumber”:7844,”employeeName”:”TURNER”,”job”:”SALESMAN”,”salary”:1500},{“employeeNumber”:7900,”employeeName”:”JAMES”,”job”:”CLERK”,”salary”:950}]}{“departmentNumber”:40,”departmentName”:”OPERATIONS”,”location”:”BOSTON”,”employees”:[]}SQL>در مثال قبلی ، پیوستن مورد نیاز به دلیل تعریف کلید خارجی به طور خودکار تشخیص داده شد. ما می توانیم صریحاً ستون کلید خارجی را با استفاده از دستورالعمل Link ارجاع دهیم.select * from graphql(‘department : dept{departmentNumber: deptnodepartmentName : dnamelocation : locemployees : emp @link (to : [“deptno”])[{employeeNumber : empnoemployeeName : enamejob : jobsalary : sal}]}’);این بار ما اطلاعات کارمندان از جمله نام بخش آنها را با استفاده از داده های بخش باز می گردانیم.select * from graphql(‘employees : emp{employeeNumber : empnoemployeeName : enamejob : jobsalary : saldept @unnest{departmentName : dname}}’);JSON_OBJECT(‘EMPLOYEENUMBER’VALUE”EMPNO”,’EMPLOYEENAME’VALUE”ENAME”,’JOB’VALUE”JOB”,’SALARY’VALUE”SAL”,UNNEST(SELECTJSON_OBJECT(‘DEPARTMENTNAME’VALUE”DNAME”NULLONNULLEMPTYONNOROWSRETURNINGJSONETAG)FR——————————————————————————————————————————————————————————————————–{“employeeNumber”:7369,”employeeName”:”SMITH”,”job”:”CLERK”,”salary”:800,”departmentName”:”RESEARCH”}{“employeeNumber”:7499,”employeeName”:”ALLEN”,”job”:”SALESMAN”,”salary”:1600,”departmentName”:”SALES”}{“employeeNumber”:7521,”employeeName”:”WARD”,”job”:”SALESMAN”,”salary”:1250,”departmentName”:”SALES”}{“employeeNumber”:7566,”employeeName”:”JONES”,”job”:”MANAGER”,”salary”:2975,”departmentName”:”RESEARCH”}{“employeeNumber”:7654,”employeeName”:”MARTIN”,”job”:”SALESMAN”,”salary”:1250,”departmentName”:”SALES”}{“employeeNumber”:7698,”employeeName”:”BLAKE”,”job”:”MANAGER”,”salary”:2850,”departmentName”:”SALES”}{“employeeNumber”:7782,”employeeName”:”CLARK”,”job”:”MANAGER”,”salary”:2450,”departmentName”:”ACCOUNTING”}{“employeeNumber”:7788,”employeeName”:”SCOTT”,”job”:”ANALYST”,”salary”:3000,”departmentName”:”RESEARCH”}{“employeeNumber”:7839,”employeeName”:”KING”,”job”:”PRESIDENT”,”salary”:5000,”departmentName”:”ACCOUNTING”}{“employeeNumber”:7844,”employeeName”:”TURNER”,”job”:”SALESMAN”,”salary”:1500,”departmentName”:”SALES”}{“employeeNumber”:7876,”employeeName”:”ADAMS”,”job”:”CLERK”,”salary”:1100,”departmentName”:”RESEARCH”}{“employeeNumber”:7900,”employeeName”:”JAMES”,”job”:”CLERK”,”salary”:950,”departmentName”:”SALES”}{“employeeNumber”:7902,”employeeName”:”FORD”,”job”:”ANALYST”,”salary”:3000,”departmentName”:”RESEARCH”}{“employeeNumber”:7934,”employeeName”:”MILLER”,”job”:”CLERK”,”salary”:1300,”departmentName”:”ACCOUNTING”}۱۴ rows selected.SQL>نماهای دوگانگی GraphQL و Jsonدو روش برای ایجاد دیدگاه های دوگانگی json وجود دارد.SQL SYNTAX – نمای با استفاده از تماس های عملکرد SQL/JSON در SQL تعریف شده است.SYNTAX GRAPHQL – نمای با استفاده از GraphQL تعریف شده است ، که در پشت صحنه به SQL تبدیل می شود.خدمات داده Data GraphQL و Oracle (ORDS)پشتیبانی GraphQL به Oracle Rest Data Services (ORDS) نسخه ۲۳٫۳ اضافه شد.

کار با آرایه‌ها در PostgreSQL

مسعود سلطانی راد — Tue, 17 Sep 2024 15:48:15 +0330

در این مقاله، با نحوه کار با آرایه‌ها در PostgreSQL و استفاده از توابع کاربردی برای دستکاری آرایه‌ها آشنا خواهید شد.معرفی نوع داده آرایه در PostgreSQLدر PostgreSQL، آرایه مجموعه‌ای از عناصری است که همگی دارای یک نوع داده مشابه هستند. آرایه‌ها می‌توانند یک‌بعدی، چندبعدی و حتی تودرتو باشند.هر نوع داده در PostgreSQL یک نوع آرایه معادل دارد. به‌عنوان مثال، نوع داده integer دارای آرایه‌ای با نوع integer[] و نوع character دارای آرایه‌ای با نوع character[] است.همچنین، اگر یک نوع داده کاربر تعریف کنید، PostgreSQL به طور خودکار نوع آرایه‌ی مرتبط با آن را ایجاد می‌کند.برای تعریف یک ستون با نوع آرایه، از سینتکس زیر استفاده می‌کنید:column_name datatype []در این سینتکس، یک آرایه یک‌بعدی از نوع datatype تعریف می‌شود.مثال: ایجاد جدول با ستون آرایهدر مثال زیر، جدولی به نام contacts ایجاد می‌کنیم که ستون phones به صورت آرایه‌ای از نوع TEXT تعریف شده است:CREATE TABLE contacts (id SERIAL PRIMARY KEY,name VARCHAR (100),phones TEXT []);در اینجا، ستون phones یک آرایه یک‌بعدی است که شماره تلفن‌های مختلف یک مخاطب را نگه می‌دارد.تعریف آرایه چندبعدیبرای تعریف آرایه‌های چندبعدی، می‌توانید از کروشه‌های اضافی استفاده کنید:column_name data_type [][]درج داده در آرایهبرای درج داده در ستون آرایه، از سینتکس زیر استفاده می‌شود:INSERT INTO contacts (name, phones)VALUES(‘John Doe’,ARRAY [ ‘(408)-589-5846′,'(408)-589-5555’ ]);در این مثال، از سازنده ARRAY برای ساختن آرایه و درج آن در جدول contacts استفاده شده است.همچنین می‌توانید از آکولادها برای درج داده استفاده کنید:INSERT INTO contacts (name, phones)VALUES(‘Lily Bush’,'{“(408)-589-5841”}’),(‘William Gate’,'{“(408)-589-5842″,”(408)-589-58423”}’);در اینجا دو ردیف به جدول contacts اضافه می‌شود.پرس و جو از داده‌های آرایهبرای بازیابی داده‌ها از جدول contacts، از دستور زیر استفاده می‌کنیم:SELECTname,phonesFROMcontacts;نتیجه:name | phones————–+———————————-John Doe | {(۴۰۸)-۵۸۹-۵۸۴۶,(۴۰۸)-۵۸۹-۵۵۵۵}Lily Bush | {(۴۰۸)-۵۸۹-۵۸۴۱}William Gate | {(408)-589-5842,(408)-589-58423}برای دسترسی به یک عنصر آرایه، می‌توانید از زیرنویس استفاده کنید:SELECTname,phones [1]FROMcontacts;نتیجه:name | phones————–+—————-John Doe | (۴۰۸)-۵۸۹-۵۸۴۶Lily Bush | (۴۰۸)-۵۸۹-۵۸۴۱William Gate | (408)-589-5842همچنین می‌توانید از عناصر آرایه در بخش WHERE برای فیلتر کردن ردیف‌ها استفاده کنید. به عنوان مثال، این کوئری مخاطبانی را پیدا می‌کند که شماره تلفن (۴۰۸)-۵۸۹-۵۸۴۲۳ را به‌عنوان دومین شماره تلفن خود دارند:SELECTnameFROMcontactsWHEREphones [2] = ‘(408)-589-58423’;نتیجه:markdownCopy codename————–William Gateاصلاح آرایه در PostgreSQLPostgreSQL به شما این امکان را می‌دهد که هر عنصر آرایه یا کل آرایه را به‌روزرسانی کنید.برای مثال، به‌روزرسانی دومین شماره تلفن William Gate:UPDATE contactsSET phones [2] = ‘(408)-589-5843’WHERE ID = 3RETURNING *;نتیجه:id | name | phones—-+————–+———————————۳ | William Gate | {(408)-589-5842,(408)-589-5843}برای به‌روزرسانی کل آرایه:UPDATEcontactsSETphones = ‘{“(408)-589-5843”}’WHEREid = 3RETURNING *;نتیجه:bashCopy codeid | name | phones—-+————–+——————۳ | William Gate | {(408)-589-5843}جستجو در آرایه PostgreSQLفرض کنید می‌خواهید بدانید چه کسی شماره تلفن (۴۰۸)-۵۸۹-۵۵۵۵ را دارد، بدون توجه به اینکه این شماره در کدام بخش از آرایه قرار دارد. برای این کار می‌توانید از تابع ANY() استفاده کنید:SELECTname,phonesFROMcontactsWHERE‘(۴۰۸)-۵۸۹-۵۵۵۵’ = ANY (phones);نتیجه:name | phones———-+———————————John Doe | {(408)-589-5846,(408)-589-5555}تبدیل آرایه‌ها به لیستPostgreSQL تابع unnest() را ارائه می‌دهد که آرایه‌ها را به یک لیست از ردیف‌ها گسترش می‌دهد. به عنوان مثال، کوئری زیر تمامی شماره‌های تلفن آرایه‌ی phones را به لیست تبدیل می‌کند:SELECTname,unnest(phones)FROMcontacts;نتیجه:name | unnest————–+—————-John Doe | (۴۰۸)-۵۸۹-۵۸۴۶John Doe | (۴۰۸)-۵۸۹-۵۵۵۵Lily Bush | (۴۰۸)-۵۸۹-۵۸۴۱William Gate | (408)-589-5843خلاصهدر PostgreSQL، آرایه مجموعه‌ای از عناصر با نوع داده مشابه است.برای تعریف آرایه یک‌بعدی در یک ستون از datatype [] استفاده کنید.برای دسترسی به عناصر آرایه از سینتکس [index] استفاده کنید.برای گسترش آرایه به یک لیست از ردیف‌ها از تابع unnest() استفاده کنید.

بهبود قابلیت‌های Auditing در Oracle Database 23ai

مسعود سلطانی راد — Wed, 11 Sep 2024 08:26:03 +0330

در این پست به بررسی برخی از بهبودهای Auditing در Oracle Database 23ai می‌پردازیم.توقف پشتیبانی از Traditional AuditingAuditing سنتی (Traditional Auditing) در Oracle 21c از رده خارج شد و در Oracle 23ai دیگر پشتیبانی نمی‌شود. توصیه می‌شود که از Unified Auditing استفاده کنید.Audit کردن ستون‌های جداگانه برای جداول و ویوهادر Oracle 23ai، می‌توانید بر روی ستون‌های جداگانه جداول و ویوها سیاست‌های Auditing ایجاد کنید. این قابلیت به شما اجازه می‌دهد تا محتوای audit trail را کاهش دهید و تنها اعمالی که روی ستون‌های مورد نظر انجام می‌شوند را ثبت کنید. برای یک ستون در جدول یا ویو می‌توانید عملیات زیر را Audit کنید:ALLALTERAUDITCOMMENTDELETEGRANTINDEXINSERTSELECTUPDATEدر ادامه مثالی از نحوه پیاده‌سازی این قابلیت آورده شده است.ایجاد جدول تستconn testuser1/testuser1@//localhost:1521/freepdb1drop table if exists audit_test_tab purge;create table audit_test_tab (id number generated always as identity,col1 varchar2(10),col2 varchar2(10),col3 varchar2(10));insert into audit_test_tab (col1, col2) values (‘apple’, ‘banana’);commit;تعریف سیاست‌های Auditسیاست جدیدی برای Audit کردن عملیات UPDATE روی ستون‌های COL1 و COL2 و SELECT روی COL2 ایجاد می‌کنیم. توجه کنید که لیستی از ستون‌ها برای عملیات Auditing به صورت کاما جدا شده ارائه می‌شود.conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdbanoaudit policy test_audit_policy;drop audit policy test_audit_policy;create audit policy test_audit_policyactions update(col1, col2) on testuser1.audit_test_tab,select(col2) on testuser1.audit_test_tabcontainer = current;audit policy test_audit_policy;بررسی trail‌های Auditدر این مرحله، trail‌های Audit برای عملیات انجام شده بر روی جدول را بررسی می‌کنیم.column event_timestamp format a30column dbusername format a10column action_name format a20column object_schema format a10column object_name format a20column sql_text format a40select event_timestamp,dbusername,action_name,object_schema,object_name,sql_textfrom unified_audit_trailwhere object_name = ‘AUDIT_TEST_TAB’order BY event_timestamp;نتیجه: هیچ ردی از Auditing در حال حاضر وجود ندارد.انجام برخی عملیاتدر ادامه برخی از عملیات بر روی جدول تست انجام می‌دهیم. بخشی از این عملیات‌ها قابل Auditing هستند.conn testuser1/testuser1@//localhost:1521/freepdb1— Not audited.insert into audit_test_tab (col1, col2) values (‘apple2’, ‘banana2’);update audit_test_tabset col3 = ‘pear’where col3 is null;commit;select id from audit_test_tab;ID———-۱۲SQL>۱عملیات قابل Auditingعملیات زیر قابل Auditing هستند:update audit_test_tabset col1 = ‘apple1’where col1 = ‘apple’;update audit_test_tabset col2 = ‘banana1’where col2 = ‘banana’;select col2 from audit_test_tab;COL2———-banana1banana2بررسی مجدد trail‌های Auditconn sys/SysPassword1@//localhost:1521/freepdb1 as sysdbacolumn event_timestamp format a30column dbusername format a10column action_name format a20column object_schema format a10column object_name format a20column sql_text format a40select event_timestamp,dbusername,action_name,object_schema,object_name,sql_textfrom unified_audit_trailwhere object_name = ‘AUDIT_TEST_TAB’order BY event_timestamp;EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME SQL_TEXT—————————— ———- ——————– ———- ——————– —————————————-۱۴-JUN-23 19.31.17.231940 PM TESTUSER1 UPDATE TESTUSER1 AUDIT_TEST_TAB update audit_test_tabset col1 = ‘apple1’where col1 = ‘apple’۱۴-JUN-23 19.31.17.248862 PM TESTUSER1 UPDATE TESTUSER1 AUDIT_TEST_TAB update audit_test_tabset col2 = ‘banana1’where col2 = ‘banana’۱۴-JUN-23 19.31.17.252646 PM TESTUSER1 SELECT TESTUSER1 AUDIT_TEST_TAB select col2 from audit_test_tabSQL>نتایج بررسیفقط عملیات مربوط به ستون‌های COL1 و COL2 Audit شده‌اند. کوئری مربوط به ستون ID و بروزرسانی ستون COL3 ثبت نشده‌اند. این ویژگی به شما امکان می‌دهد که حجم داده‌های Audit شده را کاهش داده و تنها عملیات‌های مورد نیاز را ثبت کنید.منبع مستند

مقدمه‌ای بر امنیت PostgreSQL

مسعود سلطانی راد — Thu, 15 Aug 2024 18:57:05 +0330

پایگاه‌داده‌ها مانند جام مقدس برای هکرها هستند و به همین دلیل باید با دقت بسیار بالا محافظت شوند. در این سری از مقالات، به بررسی بهترین روش‌های امنیتی برای محافظت از پایگاه‌داده‌ها خواهیم پرداخت.ما با یکی از محبوب‌ترین پایگاه‌های داده منبع‌باز، PostgreSQL، شروع می‌کنیم و چندین سطح از امنیت را که باید به آنها فکر کنید، بررسی خواهیم کرد:امنیت در سطح شبکهامنیت در سطح انتقالامنیت در سطح پایگاه‌داده۱. امنیت در سطح شبکه برای PostgreSQLدیوارهای آتشدر یک دنیای ایده‌آل، سرور PostgreSQL شما باید به‌طور کامل ایزوله باشد و هیچ اتصالی از بیرون، چه SSH یا psql، اجازه ورود نداشته باشد. متأسفانه، چنین تنظیمات ایزوله‌ای به‌طور پیش‌فرض توسط PostgreSQL پشتیبانی نمی‌شود.گام بعدی که می‌توانید برای بهبود امنیت سرور پایگاه‌داده خود انجام دهید، بستن دسترسی‌های پورت به نودی است که پایگاه‌داده روی آن اجرا می‌شود. به‌طور پیش‌فرض، PostgreSQL روی پورت TCP 5432 گوش می‌دهد. بسته به سیستم‌عامل، ممکن است راه‌های مختلفی برای بستن پورت‌های دیگر وجود داشته باشد. اما با استفاده از ابزار فایروال iptables که در اکثر توزیع‌های لینوکس موجود است، می‌توانید به شکل زیر عمل کنید:# اطمینان حاصل کنید که اتصالات برقرارشده از قبل قطع نشوند.iptables -A INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT# اجازه اتصال SSH.iptables -A INPUT -p tcp -m state –state NEW –dport 22 -j ACCEPT# اجازه اتصال به PostgreSQL.iptables -A INPUT -p tcp -m state –state NEW –dport 5432 -j ACCEPT# اجازه تمام اتصالات خروجی و بستن بقیه ورودی‌ها.iptables -A OUTPUT -j ACCEPTiptables -A INPUT -j DROPiptables -A FORWARD -j DROPنکته: هنگام به‌روزرسانی قوانین iptables، بهتر است از ابزار iptables-apply استفاده کنید که در صورت اشتباه، تغییرات را به‌طور خودکار بازگرداند.قانون PostgreSQL بالا به همه اجازه می‌دهد تا به پورت ۵۴۳۲ متصل شوند. می‌توانید آن را محدودتر کرده و فقط از برخی آدرس‌های IP یا زیرشبکه‌ها اجازه اتصال بدهید:# فقط اجازه دسترسی به پورت PostgreSQL از زیرشبکه محلی.iptables -A INPUT -p tcp -m state –state NEW –dport 5432 -s 192.168.1.0/24 -j ACCEPTدر سناریوی ایده‌آل ما، جلوگیری از تمام اتصالات ورودی به پورت ۵۴۳۲ نیازمند یک عامل محلی است که یک اتصال پایدار به نود مشتری برقرار کرده و توانایی پراکسی کردن ترافیک به نمونه PostgreSQL محلی را داشته باشد.این تکنیک به “تونل‌سازی معکوس” معروف است و می‌توان آن را با استفاده از ویژگی پورت فرواردینگ SSH به نمایش گذاشت. شما می‌توانید یک تونل معکوس با اجرای دستور زیر از نودی که پایگاه‌داده PostgreSQL روی آن در حال اجراست باز کنید:ssh -f -N -T -R 5432:localhost:5432 user@البته، باید از نود PostgreSQL قابل دسترسی باشد و SSH daemon روی آن اجرا شود. این دستور پورت ۵۴۳۲ روی سرور پایگاه‌داده را به پورت ۵۴۳۲ روی ماشین مشتری فروارد می‌کند و شما می‌توانید از طریق تونل به پایگاه‌داده متصل شوید:psql “host=localhost port=5432 user=postgres dbname=postgres”تنظیم آدرس‌های شنیداری PostgreSQL با استفاده از listen_addressesبهتر است آدرس‌هایی را که سرور برای اتصالات مشتری گوش می‌دهد، با استفاده از دستورالعمل listen_addresses در فایل پیکربندی محدود کنید. اگر نود PostgreSQL چندین رابط شبکه دارد، از این تنظیمات استفاده کنید تا مطمئن شوید سرور فقط روی رابط‌هایی که مشتری‌ها به آن متصل می‌شوند، گوش می‌دهد:listen_addresses = ‘localhost, 192.168.0.1’اگر مشتری‌ها همیشه روی همان نود قرار دارند (یا مثلاً در همان پاد Kubernetes با PostgreSQL به‌عنوان یک کانتینر جانبی قرار دارند)، غیرفعال کردن گوش دادن سوکت TCP می‌تواند شبکه را به‌طور کامل از تصویر حذف کند. تنظیم آدرس‌های گوش دادن به یک رشته خالی، باعث می‌شود سرور فقط اتصالات سوکت دامنه یونیکس را قبول کند:listen_addresses = ”۲. امنیت در سطح انتقال برای PostgreSQLبا حرکت به سمت جهانی که اکثر وب به سمت HTTPs می‌رود، دلیلی برای استفاده نکردن از رمزگذاری قوی برای اتصالات پایگاه‌داده وجود ندارد. PostgreSQL به‌طور بومی از TLS (که به‌دلایل میراثی همچنان به‌عنوان SSL در مستندات، پیکربندی و CLI نامیده می‌شود) پشتیبانی می‌کند و روش‌هایی برای استفاده از آن برای احراز هویت سرور و مشتری ارائه می‌دهد.TLS سروربرای احراز هویت سرور، ابتدا باید یک گواهی دریافت کنید که سرور به مشتری‌های متصل ارائه می‌دهد. Let’s Encrypt به‌راحتی می‌تواند به‌صورت رایگان گواهی‌های X.509 را فراهم کند، مثلاً با استفاده از ابزار CLI certbot:certbot certonly –standalone -d postgres.example.comبه خاطر داشته باشید که به‌طور پیش‌فرض certbot از چالش HTTP-01 ACME برای اعتبارسنجی درخواست گواهی استفاده می‌کند که نیاز به یک DNS معتبر برای دامنه درخواست‌شده دارد و باید به نود اشاره کرده و پورت ۸۰ باز باشد.اگر به‌دلیلی نمی‌توانید از Let’s Encrypt استفاده کنید و می‌خواهید تمام رازها را به‌صورت محلی تولید کنید، می‌توانید این کار را با استفاده از ابزار CLI openssl انجام دهید:# ایجاد یک CA سرور خودامضا شده.openssl req -sha256 -new -x509 -days 365 -nodes \-out server-ca.crt \-keyout server-ca.key# تولید CSR سرور. نام میزبان که برای اتصال به پایگاه‌داده استفاده خواهید کرد را در فیلد CN قرار دهید.openssl req -sha256 -new -nodes \-subj “/CN=postgres.example.com” \-out server.csr \-keyout server.key# امضای گواهی سرور.openssl x509 -req -sha256 -days 365 \-in server.csr \-CA server-ca.crt \-CAkey server-ca.key \-CAcreateserial \-out server.crtالبته، در محیط تولید باید اطمینان حاصل کنید که این گواهی‌ها قبل از تاریخ انقضا به‌روز شوند.TLS مشتریهنگام اتصال به یک پایگاه‌داده Postgres، می‌توانید از طیف وسیعی از روش‌های احراز هویت استفاده کنید. ما توصیه می‌کنیم از گواهی‌های مشتری استفاده کنید. احراز هویت با گواهی مشتری به سرور اجازه می‌دهد تا هویت یک مشتری متصل را با اعتبارسنجی گواهی X.509 ارائه‌شده توسط مشتری که توسط یک مرجع صدور گواهی معتبر امضا شده است، تأیید کند.بهتر است از مراجع صدور گواهی مختلف برای صدور گواهی‌های مشتری و سرور استفاده کنید، بنابراین اجازه دهید یک CA مشتری ایجاد کنیم و از آن برای امضای یک گواهی مشتری استفاده کنیم:# ایجاد یک CA مشتری خودامضا شده.openssl req -sha256 -new -x509 -days 365 -nodes \-out client-ca.crt \-keyout client-ca.key# تولید CSR مشتری. فیلد CN باید شامل نام نقش پایگاه‌داده‌ای باشد که برای اتصال به پایگاه‌داده استفاده می‌کنید.openssl req -sha256 -new -nodes \-subj “/CN=alice” \-out client.csr \-keyout server.key# امضای گواهی مشتری.openssl x509 -req -sha256 -days 365 \-in client.csr \-CA client-ca.crt \-CAkey client-ca.key \-CAcreateserial \-out client.crtتوجه داشته باشید که فیلد CommonName (CN) گواهی مشتری باید شامل نام حساب کاربری پایگاه‌داده‌ای باشد که مشتری به آن متصل می‌شود. سرور PostgreSQL از آن برای تعیین هویت مشتری استفاده خواهد کرد.پیکربندی TLSحالا که همه موارد مورد نیاز را دارید، می‌توانید سرور PostgreSQL خود را در فایل پیکربندی postgresql.conf برای پذیرش اتصالات TLS پیکربندی کنید:ssl = onssl_cert_file = ‘/path/to/server.crt’ssl_key_file = ‘/path/to/server.key’ssl_ca_file = ‘/path/to/client-ca.crt’# این تنظیم به صورت پیش‌فرض فعال است اما بهتر است برای امنیت بیشتر آن را به صورت صریح تنظیم کنید.ssl_prefer_server_ciphers = on# TLS 1.3 بالاترین سطح امنیت را فراهم می‌کند و برای زمانی که کنترل هر دو سرور و مشتری را دارید، توصیه می‌شود.ssl_min_protocol_version = ‘TLSv1.3’یک بخش دیگر از پیکربندی باقی مانده است که باید فایل احراز هویت مبتنی بر میزبان سرور PostgreSQL، یعنی pg_hba.conf، را به‌روزرسانی کنید تا برای همه اتصالات TLS را اجباری کند و مشتری‌ها را با استفاده از گواهی‌های X.509 تأیید هویت کند:# TYPE DATABASE USER ADDRESS METHODhostssl all all ::/۰ certhostssl all all ۰٫۰٫۰٫۰/۰ certاکنون مشتریانی که به سرور پایگاه داده متصل می‌شوند باید یک گواهی معتبر ارائه دهند که توسط مراجع صدور گواهی مشتری امضا شده باشد:psql “host=postgres.example.com \user=alice \dbname=postgres \sslmode=verify-full \sslrootcert=/path/to/server-ca.crt \sslcert=/path/to/client.crt \sslkey=/path/to/client.key”به یاد داشته باشید که به صورت پیش‌فرض psql تأیید گواهی سرور را انجام نمی‌دهد، بنابراین sslmode باید به verify-full یا verify-ca تنظیم شود، بسته به این که آیا شما به سرور PostgreSQL با همان نام میزبان که در فیلد CN گواهی X.509 آن رمزگذاری شده است متصل می‌شوید یا خیر.برای کاهش verbosity دستور و جلوگیری از وارد کردن مسیرهای محرمانه TLS هر بار که می‌خواهید به پایگاه داده متصل شوید، می‌توانید از یک فایل سرویس اتصال PostgreSQL استفاده کنید. این فایل به شما امکان می‌دهد که پارامترهای اتصال را به “سرویس‌ها” گروه‌بندی کنید که سپس می‌توانید از طریق یک پارامتر “service” در رشته اتصال به آن‌ها ارجاع دهید.فایل ~/.pg_service.conf را با محتوای زیر ایجاد کنید:[example]host=postgres.example.comuser=alicesslmode=verify-fullsslrootcert=/path/to/server-ca.crtsslcert=/path/to/client.crtsslkey=/path/to/client.keyحالا، وقتی که می‌خواهید به یک پایگاه داده متصل شوید، فقط کافی است نام سرویس و نام پایگاه داده‌ای که می‌خواهید به آن متصل شوید را مشخص کنید:psql “service=example dbname=postgres”امنیت در سطح پایگاه دادهمروری بر نقش‌هاتا اینجا ما نحوه محافظت از سرور پایگاه داده PostgreSQL از اتصالات غیرمجاز شبکه، استفاده از رمزگذاری قوی برای انتقال و اطمینان از این که سرور و مشتری‌ها می‌توانند به هویت یکدیگر اعتماد کنند را بررسی کردیم. یک بخش دیگر از پازل این است که تعیین کنیم کاربران پس از اتصال به پایگاه داده و تأیید هویتشان چه کاری می‌توانند انجام دهند و به چه چیزهایی دسترسی دارند. این معمولاً به عنوان مجوز یا کنترل دسترسی شناخته می‌شود.PostgreSQL یک سیستم جامع مجوز کاربران دارد که بر اساس مفهوم نقش‌ها ساخته شده است. در نسخه‌های مدرن PostgreSQL (نسخه ۸.۱ و جدیدتر)، یک “نقش” معادل با “کاربر” است، بنابراین هر نام حساب پایگاه داده‌ای که استفاده می‌کنید، مثلاً با psql (مثلاً “user=alice”)، در واقع یک نقش با ویژگی LOGIN است که به آن اجازه می‌دهد به پایگاه داده متصل شود. در واقع، دستورات SQL زیر معادل هستند:CREATE USER alice;CREATE ROLE alice LOGIN;علاوه بر توانایی ورود به سیستم، نقش‌ها می‌توانند ویژگی‌های دیگری داشته باشند که به آن‌ها اجازه می‌دهد تمام بررسی‌های مجوز را نادیده بگیرند (SUPERUSER)، پایگاه داده‌ها را ایجاد کنند (CREATEDB)، نقش‌های دیگر را ایجاد کنند (CREATEROLE) و غیره.علاوه بر ویژگی‌ها، نقش‌ها می‌توانند مجوزهایی دریافت کنند که می‌توان آن‌ها را به دو دسته تقسیم کرد: عضویت در نقش‌های دیگر و امتیازات اشیاء پایگاه داده. بیایید ببینیم که این موارد چگونه در عمل کار می‌کنند.اعطای مجوزهای نقشبرای مثال فرضی ما، ما فهرست سرورها را دنبال خواهیم کرد:CREATE TABLE server_inventory (id int PRIMARY KEY,description text,ip_address text,environment text,owner text,);به‌صورت پیش‌فرض، نصب PostgreSQL شامل یک نقش فوق‌العاده کاربر (معمولاً به نام “postgres”) است که برای راه‌اندازی اولیه پایگاه داده استفاده می‌شود. استفاده از این نقش برای همه عملیات‌های پایگاه داده معادل با استفاده همیشگی از ورود “root” در لینوکس است که هرگز توصیه نمی‌شود. در عوض، بهتر است یک نقش بدون امتیاز ایجاد کنیم و مجوزها را طبق نیاز به آن تخصیص دهیم و اصل حداقل مجوز را رعایت کنیم.به جای اختصاص مجوزها به هر کاربر/نقش جدید به صورت جداگانه، می‌توانید یک “نقش گروهی” ایجاد کنید و به دیگر نقش‌ها (که به کاربران فردی نقشه‌بندی می‌شوند) عضویت در این گروه را اعطا کنید. فرض کنید می‌خواهید به توسعه‌دهندگان خود، آلیس و باب، اجازه دهید فهرست سرور را مشاهده کنند ولی نتوانند آن را تغییر دهند:# ایجاد یک نقش گروهی که به تنهایی توانایی ورود ندارد و به آن مجوز SELECT روی جدول فهرست سرورها را بدهید.CREATE ROLE developer;GRANT SELECT ON server_inventory TO developer;# ایجاد دو حساب کاربری که با ورود به پایگاه داده امتیازات “developer” را به ارث می‌برند.CREATE ROLE alice LOGIN INHERIT;CREATE ROLE bob LOGIN INHERIT;#هر دو حساب کاربری را به گروه نقش “developer” اضافه کنید.GRANT developer TO alice, bob;حالا، وقتی که به پایگاه داده متصل شوند، هر دو آلیس و باب امتیازات گروه نقش “developer” را به ارث می‌برند و قادر خواهند بود تا کوئری‌هایی روی فهرست سرور اجرا کنند.به طور پیش‌فرض، امتیاز SELECT به همه ستون‌های جدول اعمال می‌شود، اما این الزام‌آور نیست. فرض کنید شما فقط می‌خواهید به کارآموزان خود اجازه دهید اطلاعات عمومی فهرست سرورها را مشاهده کنند بدون اینکه آن‌ها بتوانند به IP address دسترسی پیدا کنند:CREATE ROLE intern;GRANT SELECT(id, description) ON server_inventory TO intern;CREATE ROLE charlie LOGIN INHERIT;GRANT intern TO charlie;سایر امتیازات اشیاء پایگاه داده که بیشتر استفاده می‌شوند عبارتند از INSERT، UPDATE، DELETE و TRUNCATE که معادل دستورات SQL مربوطه هستند، اما شما همچنین می‌توانید مجوزهایی برای اتصال به پایگاه‌های داده خاص، ایجاد طرحواره‌های جدید یا اشیاء درون طرحواره، اجرای توابع و غیره اعطا کنید. برای مشاهده فهرست کامل به بخش Privileges در مستندات PostgreSQL مراجعه کنید.امنیت سطح سطر در PostgreSQLیکی از ویژگی‌های پیشرفته سیستم مجوز PostgreSQL امنیت سطح سطر است که به شما اجازه می‌دهد مجوزها را برای یک زیرمجموعه از سطرهای یک جدول اختصاص دهید. این شامل سطرهایی است که با دستور SELECT کوئری می‌شوند، و همچنین سطرهایی که با دستورات INSERT، UPDATE و DELETE به‌روزرسانی می‌شوند.برای شروع استفاده از امنیت سطح سطر، به دو چیز نیاز دارید: فعال‌کردن آن برای یک جدول و تعریف یک سیاست که دسترسی سطح سطر را کنترل می‌کند.با ادامه‌ی مثال قبلی، فرض کنید که می‌خواهید کاربران فقط بتوانند سرورهای خود را به‌روزرسانی کنند. ابتدا امنیت سطح سطر را برای جدول فعال کنید:ALTER TABLE server_inventory ENABLE ROW LEVEL SECURITY;بدون تعریف هیچ سیاستی، PostgreSQL به‌صورت پیش‌فرض سیاست “رد” را اعمال می‌کند، به این معنا که هیچ نقشی (به‌جز مالک جدول که معمولاً نقشی است که جدول را ایجاد کرده است) به آن دسترسی ندارد.یک سیاست امنیتی سطر یک عبارت بولی است که PostgreSQL برای هر سطری که قرار است بازگردانده یا به‌روزرسانی شود، آن را ارزیابی می‌کند. سطرهای بازگردانده شده توسط دستورات SELECT با عبارت مشخص شده در بند USING بررسی می‌شوند، در حالی که سطرهای به‌روزرسانی شده توسط دستورات INSERT، UPDATE یا DELETE با عبارت WITH CHECK بررسی می‌شوند.بیایید چند سیاست تعریف کنیم که به کاربران اجازه می‌دهد تا همه سرورها را مشاهده کنند ولی فقط سرورهای خود را به‌روزرسانی کنند، همانطور که توسط فیلد “owner” جدول تعیین می‌شود:CREATE POLICY select_all_serversON server_inventory FOR SELECTUSING (true);CREATE POLICY update_own_serversON server_inventory FOR UPDATEUSING (current_user = owner)WITH CHECK (current_user = owner);توجه داشته باشید که تنها مالک جدول می‌تواند سیاست‌های امنیتی سطر را برای آن ایجاد یا به‌روزرسانی کند.حسابرسی (Auditing)تا اینجا بیشتر در مورد اقدامات امنیتی پیشگیرانه صحبت کرده‌ایم. با پیروی از یکی از اصول اساسی امنیت، یعنی دفاع در عمق، بررسی کردیم که چگونه این اقدامات بر روی یکدیگر لایه‌بندی می‌شوند تا به کاهش پیشرفت فرضی یک مهاجم در سیستم کمک کنند.حفظ یک مسیر دقیق و دقیق از سوابق یکی از ویژگی‌های امنیتی سیستم است که اغلب نادیده گرفته می‌شود. نظارت بر دسترسی‌های سطح شبکه یا سطح گره به سرور پایگاه داده شما خارج از حوزه این پست است، اما بیایید نگاهی به گزینه‌های موجود در مورد خود سرور PostgreSQL بیندازیم.ساده‌ترین کاری که می‌توانید برای افزایش دید در مورد اتفاقاتی که در داخل پایگاه داده می‌افتد انجام دهید، فعال‌کردن لاگ‌برداری مفصل است. دستورات زیر را به فایل پیکربندی سرور اضافه کنید تا لاگ‌برداری از تمام تلاش‌های اتصال و تمام دستورات SQL اجرا شده فعال شود:#ثبت تلاش‌های موفق و ناموفق اتصال.log_connections = on# ثبت نشست‌های خاتمه یافته.log_disconnections = on#ثبت تمام دستورات SQL اجرا شده.log_statement = allمتأسفانه، این تقریباً تمام کاری است که می‌توانید با نصب استاندارد PostgreSQL به‌صورت خودمیزبان انجام دهید. البته این بهتر از هیچ است، اما به‌خوبی در مقیاس بزرگتر از چند سرور پایگاه داده و استفاده از “grep” ساده برای جستجوی لاگ‌ها کار نمی‌کند.برای یک راهکار پیشرفته‌تر حسابرسی PostgreSQL، می‌توانید از افزونه شخص ثالثی مثل pgAudit استفاده کنید. اگر از یک نمونه PostgreSQL خودمیزبان استفاده می‌کنید، باید افزونه را به صورت دستی نصب کنید. برخی نسخه‌های میزبانی شده مانند AWS RDS از آن به‌صورت پیش‌فرض پشتیبانی می‌کنند، بنابراین فقط نیاز به فعال‌کردن آن دارید.pgAudit ساختار و جزئیات بیشتری به دستورات لاگ شده اضافه می‌کند. با این حال، به یاد داشته باشید که همچنان مبتنی بر لاگ‌ها است، که استفاده از آن را در صورتی که بخواهید لاگ‌های حسابرسی خود را به صورت ساختارمند به یک سیستم SIEM خارجی برای تجزیه و تحلیل دقیق ارسال کنید، چالش‌برانگیز می‌کند.

پیش‌فرض‌های Bigfile Tablespace در Oracle Database 23ai

مسعود سلطانی راد — Wed, 07 Aug 2024 09:18:43 +0330

در نسخه Oracle Database 23ai، به‌طور پیش‌فرض اکثر Tablespaces به‌صورت Bigfile ایجاد می‌شوند. این تغییر از نسخه‌های قبلی Oracle آغاز شد و Bigfile Tablespaces از Oracle 10g به این سیستم معرفی شدند.Tablespaces پیش‌فرض در Oracle Database 23aiدر Oracle 23ai، بیشتر Tablespaces مانند SYSAUX، SYSTEM و UNDOTBS1 به‌طور پیش‌فرض به‌صورت Bigfile ایجاد می‌شوند، به جز Tablespace TEMP که همچنان به‌صورت Smallfile باقی می‌ماند.مثال:conn / as sysdbaselect tablespace_name, bigfilefrom dba_tablespacesorder by 1;خروجی:TABLESPACE_NAME BIG—————————— —SYSAUX YESSYSTEM YESTEMP NOUNDOTBS1 YESUSERS YESرفتار در Pluggable Databaseدر Pluggable Database‌ها، رفتار مشابهی مشاهده می‌شود، با این تفاوت که در PDBها، Tablespace USERS به‌طور پیش‌فرض به‌صورت Smallfile ایجاد می‌شود. این رفتار در نسخه ۲۳ai استاندارد شده است.مثال:alter session set container=freepdb1;select tablespace_name, bigfilefrom dba_tablespacesorder by 1;خروجی:TABLESPACE_NAME BIG—————————— —SYSAUX YESSYSTEM YESTEMP NOUNDOTBS1 YESUSERS NOایجاد Tablespaces جدیدهنگام ایجاد یک Tablespace جدید بدون تنظیم صریح اندازه فایل به Bigfile یا Smallfile، Oracle 23ai به‌طور پیش‌فرض آن را به صورت Bigfile ایجاد می‌کند. این امر برای کاربران که از Oracle Managed Files (OMF) استفاده می‌کنند، ساده‌سازی بیشتری را فراهم می‌کند.مثال:create tablespace new_ts datafile size 2g;select tablespace_name, bigfilefrom dba_tablespacesorder by 1;خروجی:TABLESPACE_NAME BIG—————————— —NEW_TS YESSYSAUX YESSYSTEM YESTEMP NOUNDOTBS1 YESUSERS NOتغییرات نسبت به نسخه‌های قبلیاین پیش‌فرض‌های جدید در نسخه ۲۳٫۴ معرفی شده‌اند. در نسخه ۲۳٫۳ و قبل از آن، Tablespaces به‌طور پیش‌فرض به‌صورت Smallfile ایجاد می‌شدند.مثال در نسخه ۲۳٫۳:conn / as sysdbaselect tablespace_name, bigfilefrom dba_tablespacesorder by 1;خروجی:TABLESPACE_NAME BIG—————————— —SYSAUX NOSYSTEM NOTEMP NOUNDOTBS1 NOUSERS NOایجاد یک Tablespace جدید:create tablespace new_ts datafile size 2g;select tablespace_name, bigfilefrom dba_tablespacesorder by 1;خروجی:TABLESPACE_NAME BIG—————————— —NEW_TS NOSYSAUX NOSYSTEM NOTEMP NOUNDOTBS1 NOUSERS NOمنبع مستندبرای اطلاعات بیشتر و جزئیات دقیق‌تر، می‌توانید به مستندات رسمی Oracle مراجعه کنید.

راهنمای کامل کپی پایگاه داده PostgreSQL

مسعود سلطانی راد — Wed, 31 Jul 2024 12:44:01 +0330

در این راهنمای جامع، خواهید آموخت که چگونه یک پایگاه داده PostgreSQL را در همان سرور یا از یک سرور به سرور دیگر کپی کنید.کپی پایگاه داده PostgreSQL در همان سرورگاهی اوقات نیاز دارید تا یک پایگاه داده PostgreSQL را برای اهداف آزمایش در همان سرور کپی کنید. PostgreSQL این کار را با استفاده از عبارت CREATE DATABASE بسیار آسان کرده است:CREATE DATABASE targetdbWITH TEMPLATE sourcedb;این دستور، پایگاه داده sourcedb را به targetdb کپی می‌کند. به عنوان مثال، برای کپی کردن پایگاه داده نمونه dvdrental به dvdrental_test، از دستور زیر استفاده کنید:CREATE DATABASE dvdrental_testWITH TEMPLATE dvdrental;بسته به اندازه پایگاه داده منبع، این فرآیند ممکن است کمی زمان ببرد. اگر پایگاه داده dvdrental دارای اتصالات فعال باشد، با خطای زیر مواجه خواهید شد:ERROR: source database “dvdrental” is being accessed by other usersDETAIL: There is 1 other session using the database.برای مشاهده اتصالات فعال، از کوئری زیر استفاده کنید:SELECT pid, usename, client_addrFROM pg_stat_activityWHERE datname =’dvdrental’;برای خاتمه دادن به اتصالات فعال، از کوئری زیر استفاده کنید:SELECT pg_terminate_backend (pid)FROM pg_stat_activityWHERE datname = ‘dvdrental’;سپس می‌توانید دستور CREATE DATABASE WITH TEMPLATE را دوباره اجرا کنید.کپی پایگاه داده PostgreSQL از یک سرور به سرور دیگرراه‌های مختلفی برای کپی کردن پایگاه داده بین سرورهای PostgreSQL وجود دارد. اگر اندازه پایگاه داده بزرگ است و ارتباط بین سرورها کند است، می‌توانید از روش زیر استفاده کنید:پایگاه داده منبع را در یک فایل تخلیه کنید:pg_dump -U postgres -d sourcedb -f sourcedb.sqlفایل dump را در سرور راه دور کپی کنید.یک پایگاه داده جدید در سرور راه دور ایجاد کنید:CREATE DATABASE targetdb;فایل dump را در سرور راه دور بازیابی کنید:psql -U postgres -d targetdb -f sourcedb.sqlمثال: کپی کردن پایگاه داده dvdrentalبرای کپی کردن پایگاه داده dvdrental از سرور محلی به سرور راه دور، مراحل زیر را دنبال کنید:پایگاه داده dvdrental را در یک فایل dump قرار دهید:pg_dump -U postgres -O dvdrental -f dvdrental.sqlفایل dump را در سرور راه دور کپی کنید.پایگاه داده dvdrental را در سرور راه دور ایجاد کنید:CREATE DATABASE dvdrental;فایل dump dvdrental.sql را در سرور راه دور بازیابی کنید:psql -U postgres -d dvdrental -f dvdrental.sqlاگر ارتباط بین سرورها سریع است و حجم دیتابیس بزرگ نیست، می‌توانید از دستور زیر استفاده کنید:pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdbبه عنوان مثال، برای کپی کردن پایگاه داده dvdrental از سرور localhost به سرور راه دور، می‌توانید دستور زیر را اجرا کنید:pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrentalنتیجه‌گیریدر این راهنما، نحوه کپی کردن یک پایگاه داده PostgreSQL در همان سرور و همچنین از یک سرور به سرور دیگر را یاد گرفتید.

بهینه‌سازی پایگاه داده Oracle 23ai برای مدیریت پارتیشن‌ها

مسعود سلطانی راد — Wed, 24 Jul 2024 09:40:58 +0330

Oracle 23ai با اضافه کردن دو ستون جدید به نمای ALL_TAB_PARTITIONS، فرآیند نمایش اطلاعات پارتیشن‌ها را بسیار ساده‌تر و بهینه‌تر کرده است. این ستون‌ها HIGH_VALUE_CLOB و HIGH_VALUE_JSON هستند که دسترسی به داده‌های با ارزش بالا را راحت‌تر می‌کنند.مشکلما یک جدول پارتیشن‌بندی شده با چهار پارتیشن ایجاد می‌کنیم:drop table if exists part_tab purge;create table part_tab (created_date date not null,some_data varchar2(100) not null)partition by range (created_date) (partition part_2021 values less than (to_date(‘2022-01-01′,’YYYY-MM-DD’)),partition part_2022 values less than (to_date(‘2023-01-01′,’YYYY-MM-DD’)),partition part_2023 values less than (to_date(‘2024-01-01′,’YYYY-MM-DD’)),partition part_2024 values less than (to_date(‘2025-01-01′,’YYYY-MM-DD’)));برای بررسی مرزهای پارتیشن‌ها، باید مقدار ستون HIGH_VALUE را از نمای USER_TAB_PARTITIONS جستجو کنیم:set linesize 140 long 100 longchunksize 100column partition_name format a20column high_value format a85select partition_name,high_valuefrom user_tab_partitionswhere table_name = ‘PART_TAB’order by 1;این روش در SQL*Plus به خوبی کار می‌کند، اما کار با انواع داده‌های LONG می‌تواند مشکل‌ساز باشد، چرا که از زمان Oracle8i 8.1.6 منسوخ شده‌اند.راه‌حلOracle 23ai ستون‌های HIGH_VALUE_CLOB و HIGH_VALUE_JSON را به نماهای {PDB|DBA|ALL|USER}_TAB_PARTITIONS اضافه کرده است. این کار دسترسی به داده‌های با ارزش بالا را ساده‌تر کرده و استفاده برنامه‌ریزی شده از آن‌ها را راحت‌تر می‌کند.ابتدا ستون HIGH_VALUE_CLOB را که یک نوع داده CLOB است پرس و جو می‌کنیم:column high_value_clob format a85select partition_name,high_value_clobfrom user_tab_partitionswhere table_name = ‘PART_TAB’order by 1;سپس ستون HIGH_VALUE_JSON را که یک نوع داده JSON است، پرس و جو می‌کنیم:column high_value_json format a40select partition_name,high_value_jsonfrom user_tab_partitionswhere table_name = ‘PART_TAB’order by 1;SQL*Plus می‌داند چگونه یک نوع داده JSON را مدیریت کند، اما برای ابزارهای دیگر ممکن است نیاز به سریال‌سازی داده‌های JSON یا استخراج مقدار به عنوان یک نوع داده معتبر باشد. برای مثال، می‌توانیم مقدار را استخراج کرده و به عنوان یک نوع داده DATE برگردانیم:alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;column high_value_json format a40column high_value_date format a20select partition_name,json_serialize(high_value_json) as high_value_json,json_value(high_value_json, ‘$.high_value’ returning date) as high_value_datefrom user_tab_partitionswhere table_name = ‘PART_TAB’order by 1;نتیجه‌گیریاین تغییرات در Oracle 23ai باعث بهبود کارایی و ساده‌سازی مدیریت پارتیشن‌ها شده و مشکلات ناشی از استفاده از داده‌های LONG را برطرف کرده است. این به‌روزرسانی‌ها برای برنامه‌نویسان و مدیران پایگاه داده بسیار مفید است و به بهینه‌سازی فرآیندهای کاری کمک می‌کند.منبع مستند

مقدمه‌ای بر نوع UUID در Postgres

مسعود سلطانی راد — Wed, 17 Jul 2024 08:29:55 +0330

در این آموزش، با نوع داده PostgreSQL UUID و نحوه تولید مقادیر UUID با استفاده از یک ماژول ارائه شده آشنا خواهید شد.UUID مخفف Universal Unique Identifier است که توسط RFC 4122 و سایر استانداردهای مرتبط تعریف شده است.مقدار UUID یک کمیت ۱۲۸ بیتی است که توسط یک الگوریتم تولید می‌شود و در سراسر جهان با استفاده از همان الگوریتم، یکتایی را تضمین می‌کند.مثال‌هایی از مقادیر UUID۴۰e6215d-b5c6-4896-987c-f30f3678f608۶ecd8c99-4036-403d-bf84-cf8400f67836۳f333df6-90a4-4fda-8dd3-9485d27cee36یک UUID یک توالی از ۳۲ رقم هگزادسیمال است که در گروه‌هایی با خط فاصله از هم جدا شده‌اند.به دلیل ویژگی یکتایی خود، UUID اغلب در سیستم‌های توزیع شده یافت می‌شود زیرا تضمین بهتری نسبت به نوع داده SERIAL برای یکتایی در یک پایگاه داده واحد دارد.برای ذخیره مقادیر UUID در پایگاه داده Postgre، از نوع داده UUID استفاده می‌کنید.تولید مقادیر UUIDPostgre یک تابع برای تولید UUID فراهم می‌کند:gen_random_uuid()تابع gen_random_uuid() یک UUID نسخه ۴ (تصادفی) برمی‌گرداند. به عنوان مثال:SELECT gen_random_uuid();خروجی:gen_random_uuid————————————–d6eb621f-6dd0-4cdc-93f5-07f51b249b51(۱ row)ایجاد یک جدول با ستون UUIDما یک جدول ایجاد می‌کنیم که کلید اصلی آن از نوع داده UUID است. علاوه بر این، مقادیر ستون کلید اصلی به طور خودکار با استفاده از تابع gen_random_uuid() تولید می‌شوند.ابتدا، جدول contacts را ایجاد کنید:CREATE TABLE contacts (contact_id uuid DEFAULT gen_random_uuid(),first_name VARCHAR NOT NULL,last_name VARCHAR NOT NULL,email VARCHAR NOT NULL,phone VARCHAR,PRIMARY KEY (contact_id));در این دستور، نوع داده ستون contact_id UUID است.ستون contact_id دارای یک مقدار پیش‌فرض است که توسط تابع gen_random_uuid() فراهم می‌شود. بنابراین، هر زمان که یک سطر جدید وارد کنید بدون مشخص کردن مقدار برای ستون contact_id، Postgreتابع gen_random_uuid() ا برای تولید مقدار آن فراخوانی می‌کند.سپس، برخی داده‌ها را به جدول contacts وارد کنید:INSERT INTO contacts ( first_name, last_name, email, phone)VALUES(‘John’, ‘Smith’, ‘john.smith@example.com’, ‘۴۰۸-۲۳۷-۲۳۴۵’),(‘Jane’, ‘Smith’, ‘jane.smith@example.com’, ‘408-237-2344’),(‘Alex’, ‘Smith’, ‘alex.smith@example.com’, ‘408-237-2343’)RETURNING *;خروجی:contact_id | first_name | last_name | email | phone————————————–+————+———–+————————+————–ca61da8c-938a-48a6-8eb6-55aa08cd1b08 | John | Smith | john.smith@example.com | 408-237-2345fe2af584-8576-4d0e-b10d-6ec970732f8e | Jane | Smith | jane.smith@example.com | 408-237-2344۱۴۱aefe8-f553-43b9-bfbf-91361e83b15e | Alex | Smith | alex.smith@example.com | 408-237-2343(۳ rows)خروجی نشان می‌دهد که ستون contact_id توسط مقادیر UUID تولید شده توسط تابع gen_random_uuid() پر شده است.استفاده از ماژول uuid-ossp در نسخه‌های قدیمی Postgreاگر از نسخه قدیمی Postgre استفاده می‌کنید، نیاز به استفاده از یک ماژول شخص ثالث به نام uuid-ossp دارید که الگوریتم‌های خاصی برای تولید UUID فراهم می‌کند.برای نصب ماژول uuid-ossp، از دستور CREATE EXTENSION به شرح زیر استفاده کنید:CREATE EXTENSION IF NOT EXISTS “uuid-ossp”;عبارت IF NOT EXISTS به شما اجازه می‌دهد از نصب مجدد ماژول جلوگیری کنید.اگر می‌خواهید یک مقدار UUID تولید کنید، می‌توانید از تابع uuid_generate_v4() استفاده کنید. به عنوان مثال:SELECT uuid_generate_v4();خروجی:uuid_generate_v4————————————–۳۵۱c1afe-21b2-486c-951b-66bc9e852530(۱ row)برای اطلاعات بیشتر در مورد توابع تولید UUID، به مستندات ماژول uuid-ossp مراجعه کنید.خلاصهUUID مخفف Universal Unique Identifier است.از تابع gen_random_uuid() برای تولید یک UUID نسخه ۴ (تصادفی) استفاده کنید.

بررسی و تبدیل ستون‌های JSON مبتنی بر متن در Oracle 23c با استفاده از JSON_TYPE_CONVERTIBLE_CHECK

مسعود سلطانی راد — Tue, 09 Jul 2024 06:21:38 +0330

Oracle 23c جدیدترین نسخه پایگاه داده Oracle، ابزارهای پیشرفته‌ای را برای کار با داده‌های JSON معرفی کرده است. یکی از این ابزارها، رویه JSON_TYPE_CONVERTIBLE_CHECK در بسته DBMS_JSON است که برای بررسی و تایید قابلیت تبدیل ستون‌های JSON مبتنی بر متن به نوع داده‌ی JSON استفاده می‌شود. در این مقاله به نحوه استفاده از این رویه و حل مشکلات متداول در این فرآیند می‌پردازیم.مشکلقبل از Oracle 21c، داده‌های JSON به صورت متن ذخیره می‌شدند، معمولاً در ستون‌هایی با انواع داده‌های BLOB، CLOB یا VARCHAR2. با معرفی نوع داده‌ی JSON در Oracle 21c، نیاز به تبدیل این ستون‌ها به نوع داده‌ی جدید برای بهبود عملکرد و کارایی احساس شد. این فرآیند می‌تواند با روش‌های مختلفی انجام شود:ایجاد جدول با استفاده از انتخاب (CTAS)پمپ دادهتعریف مجدد جدول آنلایناضافه کردن ستون جدید و انجام به‌روزرسانی DMLبا این حال، پیش از شروع تبدیل، باید مطمئن شویم که داده‌های موجود در ستون‌ها برای تبدیل مناسب هستند.راه حل: استفاده از DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECKدر Oracle 23c، رویه JSON_TYPE_CONVERTIBLE_CHECK به بسته DBMS_JSON اضافه شده است. این رویه به شما اجازه می‌دهد تا قبل از شروع فرآیند تبدیل، قابلیت تبدیل داده‌های JSON مبتنی بر متن را بررسی کنید.PROCEDURE JSON_TYPE_CONVERTIBLE_CHECKPROCEDURE JSON_TYPE_CONVERTIBLE_CHECKArgument Name Type In/Out Default?—————————— ———————– —— ——–OWNER VARCHAR2 INTABLENAME VARCHAR2 INCOLUMNNAME VARCHAR2 INSTATUSTABLENAME VARCHAR2 INFASTCHECK BOOLEAN IN DEFAULTAPPENDSTATUS BOOLEAN IN DEFAULTمراحل اجرای رویه JSON_TYPE_CONVERTIBLE_CHECKایجاد جدول تستdrop table if exists json_data_precheck purge;drop table if exists json_data purge;create table json_data (id number generated always as identity,data clob);افزودن داده‌های JSON به جدول تستinsert into json_data (data)values (null);insert into json_data (data)values (‘{}’);insert into json_data (data)values (‘{“product”:”banana”, “quantity”:10}’);commit;اجرای رویه JSON_TYPE_CONVERTIBLE_CHECKbegindbms_json.json_type_convertible_check(owner => ‘testuser1’,tablename => ‘json_data’,columnname => ‘data’,statustablename => ‘json_data_precheck’);end;/بررسی نتایجselect * from json_data_precheck;نتایج نشان می‌دهد که فرآیند تبدیل بدون خطا تکمیل شده است.افزودن رکورد مشکل‌دار و اجرای مجدد رویهinsert into json_data (data)values (‘banana’);commit;drop table if exists json_data_precheck purge;begindbms_json.json_type_convertible_check(owner => ‘testuser1’,tablename => ‘json_data’,columnname => ‘data’,statustablename => ‘json_data_precheck’);end;/این بار نتایج نشان می‌دهد که یک خطا وجود دارد.اصلاح داده‌های مشکل‌دار و اجرای مجدد رویهupdate json_dataset data = ‘{“product”:”banana”, “quantity”:1}’where id = 4;commit;drop table if exists json_data_precheck purge;begindbms_json.json_type_convertible_check(owner => ‘testuser1’,tablename => ‘json_data’,columnname => ‘data’,statustablename => ‘json_data_precheck’);end;/تبدیل داده‌ها به ستون JSONپس از تایید صحت داده‌ها، می‌توانید فرآیند تبدیل را انجام دهید:alter table json_data add (data2 json);update json_dataset data2 = JSON(data);alter table json_data drop column data;alter table json_data rename column data2 to data;اکنون نوع داده‌ی ستون JSON است و حاوی داده‌های تبدیل شده می‌باشد.نتیجه‌گیریرویه JSON_TYPE_CONVERTIBLE_CHECK در Oracle 23c ابزاری قدرتمند برای بررسی و تبدیل داده‌های JSON مبتنی بر متن به نوع داده‌ی JSON است. این رویه به شما کمک می‌کند تا قبل از شروع فرآیند تبدیل، اطمینان حاصل کنید که داده‌های شما برای این تبدیل مناسب هستند و مشکلات احتمالی را پیش از تبدیل برطرف کنید. این مقاله به شما نشان داد که چگونه می‌توانید از این ابزار استفاده کنید و فرآیند تبدیل را به طور کامل مدیریت کنید.منبع مستند

چگونه اندازه (SIZE) اشیاء پایگاه داده را در PostgreSQL بدست آوریم

مسعود سلطانی راد — Wed, 03 Jul 2024 09:36:54 +0330

در این آموزش یاد می گیرید که چگونه اندازه اشیاء پایگاه داده Postgresql شامل پایگاه داده ها، جداول، فهرست ها، جدول ها و مقادیر را بدست آورید.دریافت اندازه جدول PostgreSQLبرای بدست آوردن اندازه یک جدول خاص، از تابع ()pg_relation_size استفاده می کنید. به عنوان مثال می توانید اندازه جدول actor را در پایگاه داده نمونه dvdrental به صورت زیر بدست آورید:select pg_relation_size(‘actor’);pg_relation_size——————۱۶۳۸۴خروجی این تابع در مبنای byte می باشد که برای خواناتر شدن از تابع pg_size_pretty می توان استفاده کرد.تابع pg_size_pretty یک عدد را با استفاده از بایت، کیلوبایت، مگابایت، گیگابایت یا TB به طور مناسب فرمت می‌کند. مثلا:SELECTpg_size_pretty (pg_relation_size(‘actor’)) size;size——-۱۶ kB(۱ row)همانطور که مشاهده می کنید خروجی بر مبنای KB می باشد.توجه داشته باشید که تابع ()pg_relation_size فقط اندازه جدول را برمی‌گرداند، بدون اینکه شاخص‌ها یا اشیاء اضافی را شامل شود.برای بدست آوردن اندازه کل جدول، از تابع pg_total_relation_size() استفاده می کنید. به عنوان مثال، عبارت زیر از pg_total_relation_size() برای بازیابی اندازه کل جدول actor استفاده می کند:SELECT pg_size_pretty(pg_total_relation_size (‘actor’)) size;size——-۷۲ kB(۱ row)می‌توانید از تابع pg_total_relation_size() برای یافتن اندازه بزرگ‌ترین جداول از جمله indexها استفاده کنید.به عنوان مثال، کوئری زیر ۵ جدول بزرگ را در پایگاه داده dvdrental برمی گرداند:SELECTrelname AS “relation”,pg_size_pretty (pg_total_relation_size (C .oid)) AS “total_size”FROMpg_class CLEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)WHEREnspname NOT IN (‘pg_catalog’,‘information_schema’)AND C .relkind <> ‘i’AND nspname !~ ‘^pg_toast’ORDER BYpg_total_relation_size (C .oid) DESCLIMIT 5;relation | total_size————+————rental | ۲۳۵۲ kBpayment | ۱۸۱۶ kBfilm | ۹۳۶ kBfilm_actor | 488 kBinventory | ۴۴۰ kB(۵ rows)دریافت اندازه های پایگاه داده PostgreSQLبرای بدست آوردن اندازه کل پایگاه داده، از تابع ()pg_database_size استفاده می کنید. به عنوان مثال، عبارت زیر اندازه پایگاه داده dvdrental را برمی گرداند:SELECT pg_size_pretty (pg_database_size (‘dvdrental’)) size;size——-۱۵ MB(۱ row)برای به دست آوردن اندازه هر پایگاه داده در سرور پایگاه داده فعلی، از عبارت زیر استفاده می کنید:SELECTpg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS sizeFROM pg_database;datname | size———–+———postgres | ۸۴۵۲ kBtemplate1 | 7892 kBtemplate0 | 7681 kBdvdrental | 15 MB(۴ rows)دریافت اندازه index PostgreSQLبرای به دست آوردن اندازه کل همه index های پیوست شده به یک جدول، از تابع ()pg_indexes_size استفاده می کنید.تابع pg_indexes_size() نام OID یا جدول را به عنوان آرگومان می پذیرد و کل فضای دیسک استفاده شده توسط همه شاخص های متصل به آن جدول را برمی گرداند.به عنوان مثال، برای به دست آوردن اندازه کل همه شاخص های پیوست شده به جدول فیلم، از عبارت زیر استفاده می کنید:SELECT pg_size_pretty (pg_indexes_size(‘actor’)) size;size——-۳۲ kB(۱ row)دریافت اندازه‌tablespace PostgreSQLبرای بدست آوردن اندازه tablespace، از تابع ()pg_tablespace_size استفاده می کنید.تابع pg_tablespace_size() نام tablespace را می پذیرد و اندازه را بر حسب byte برمی گرداند. برای مثال، عبارت زیر اندازه tablespace pg_default را برمی‌گرداند:SELECT pg_size_pretty(pg_tablespace_size (‘pg_default’)) size;size——-۴۸ MB(۱ row)دریافت اندازه value PostgreSQLبرای پیدا کردن مقدار فضای مورد نیاز برای ذخیره یک مقدار خاص، از تابع ()pg_column_size استفاده می کنید، به عنوان مثال:SELECTpg_column_size(5 :: smallint) smallint_size,pg_column_size(5 :: int) int_size,pg_column_size(5 :: bigint) bigint_size;smallint_size | int_size | bigint_size—————+———-+————-۲ | ۴ | ۸(۱ row)خلاصهاز تابع pg_size_pretty() برای فرمت اندازه استفاده کنید.برای بدست آوردن اندازه جدول از تابع pg_relation_size() استفاده کنید.از تابع pg_total_relation_size() برای بدست آوردن اندازه کل جدول استفاده کنید.از تابع pg_database_size() برای بدست آوردن اندازه پایگاه داده استفاده کنید.برای بدست آوردن اندازه یک شاخص از تابع pg_indexes_size() استفاده کنید.از تابع pg_total_index_size() برای به دست آوردن اندازه همه شاخص های یک جدول استفاده کنید.از تابع pg_tablespace_size() برای بدست آوردن اندازه جدول استفاده کنید.از تابع pg_column_size() برای به دست آوردن اندازه یک ستون از یک نوع خاص استفاده کنید.

Bigfile Tablespace Shrink در Oracle Database 23AI

مسعود سلطانی راد — Mon, 24 Jun 2024 09:30:15 +0330

از پایگاه داده Oracle 23ai به بعد، می‌توانیم از بسته DBMS_SPACE برای کوچک کردن یک bigfile tablespace برای بازیابی فضای بلااستفاده استفاده کنیم.راه اندازی محیط تستبرای اجرای برخی از تست ها به یک tablespace نیاز داریم. از نسخه ۲۳AI فایل ها در tablespace ها از به صورت پیش فرض از نوع BIgfile هستند، بنابراین نیازی به تعیین صریح آن نداریم.onn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba— Create a tablespace and user for the test.drop user if exists reclaim_user cascade;drop tablespace if exists reclaim_ts including contents and datafiles;create tablespace reclaim_ts datafile size 10m autoextend on next 1m;create user reclaim_user identified by reclaim_user default tablespace reclaim_ts quota unlimited on reclaim_ts;grant create session, create table to reclaim_user;grant select_catalog_role to reclaim_user;— Create and populate two tables in the test schema.conn reclaim_user/reclaim_user@//localhost:1521/freepdb1create table t1 (id number,col1 varchar2(4000),col2 varchar2(4000),constraint t1_pk primary key (id));create table t2 (id number,col1 varchar2(4000),col2 varchar2(4000),constraint t2_pk primary key (id));insert /*+append*/ into t1select rownum, rpad(‘x’, 4000, ‘x’), rpad(‘x’, 4000, ‘x’)from dualconnect by level <= 100000;commit;insert /*+append*/ into t2select rownum, rpad(‘x’, 4000, ‘x’), rpad(‘x’, 4000, ‘x’)from dualconnect by level <= 100000;commit;exec dbms_stats.gather_table_stats(null, ‘t1’);exec dbms_stats.gather_table_stats(null, ‘t2’);اندازه فایل داده مرتبط با tablespace و جداول را بررسی می کنیم.select tablespace_name, blocks, bytes/1024/1024 as size_mbfrom dba_data_fileswhere tablespace_name = ‘RECLAIM_TS’;TABLESPACE_NAME BLOCKS SIZE_MB—————————— ———- ———-RECLAIM_TS ۴۲۷۵۲۰ ۳۳۴۰SQL>column table_name format a10select table_name, blocks, (blocks*8)/1024 as size_mbfrom user_tableswhere table_name in (‘T1’, ‘T2’)order by 1;TABLE_NAME BLOCKS SIZE_MB———- ———- ———-T1 ۲۰۰۶۹۶ ۱۵۶۷٫۹۳۷۵T2 ۲۰۰۶۹۴ ۱۵۶۷٫۹۲۱۸۸SQL>ما جدول اول را Truncate می کنیم و قبل از شروع segment های جدول، یک gap در فایل داده باقی می گذاریم.truncate table t1;exec dbms_stats.gather_table_stats(null, ‘t1’);ارتباط با ماجهت دریافت خدمات مشاوره، آموزش و نگهداری پایگاه داده اوراکل با ما در ارتباط باشدBigfile Tablespace را آنالیز کنیدما یک تجزیه و تحلیل انجام می دهیم تا ببینیم با انجام یک Shrink چقدر فضای ذخیره می کنیم. ما رویه SHRINK_SPACE را در بسته DBMS_SPACE فراخوانی می‌کنیم که نام tablespace ومقدار کوچک شدن TS_MODE_ANALYZE ارسال می‌شود.conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdbaset serveroutput onexecute dbms_space.shrink_tablespace(‘RECLAIM_TS’, shrink_mode => dbms_space.ts_mode_analyze);——————-ANALYZE RESULT——————-Total Movable Objects: 2Total Movable Size(GB): 1.56Original Datafile Size(GB): 3.39Suggested Target Size(GB): 3.19Process Time: +00 00:00:00.053777PL/SQL procedure successfully completed.SQL>فکر نمی‌کند بتوانیم فضای زیادی را ذخیره کنیم، که مشکوک به نظر می‌رسد زیرا یک جدول را truncate کرده‌ایم، که تقریباً نیمی از فضای فایل داده را اشغال می‌کند.کوچک کردن Bigfile Tablespaceما با فراخوانی رویه SHRINK_SPACE با نام tablespace یک عملیات Shrink را اجرا می کنیم.set serveroutput onexecute dbms_space.shrink_tablespace(‘RECLAIM_TS’);——————-SHRINK RESULT——————-Total Moved Objects: 2Total Moved Size(GB): 1.56Original Datafile Size(GB): 3.26New Datafile Size(GB): 1.63Process Time: +00 00:00:30.586722PL/SQL procedure successfully completed.SQL>علیرغم آنچه تجزیه و تحلیل گفت، ما فایل داده مرتبط را به تقریباً نصف اندازه اصلی آن کاهش داده ایم.دستور قبلی معادل فراخوانی رویه با حالت کوچک کردن TS_MODE_SHRINK و اندازه هدف TS_TARGET_MAX_SHRINK است.اطلاعات تکمیلیدر اینجا برخی از اطلاعات اضافی در مورد کوچک کردن جدول های فایل بزرگ وجود دارد.اشیاء برای فشرده سازی بخش های موجود در فایل داده منتقل می شوند، بنابراین تمام فضای استفاده نشده در انتهای فایل داده قرار می گیرد. این اجازه می دهد تا فایل داده برای بازیابی فضای استفاده نشده کوچک شود.حرکت‌های آنلاین از طریق SHRINK_SPACE همه محدودیت‌های مرتبط با جدول تغییر متداول را ندارند… MOVE، علی‌رغم آنچه در مستندات می‌گوید. مرحله تجزیه و تحلیل نشان می دهد که آیا اشیاء پشتیبانی نشده وجود دارد یا خیر.حالت کوچک شدن TS_MODE_SHRINK_FORCE یک حرکت آفلاین را برای اشیایی انجام می دهد که از حرکت آنلاین پشتیبانی نمی کنند. اگر حرکت آفلاین باعث ایجاد مشکل در برنامه شما شد از این گزینه استفاده نکنید.اگر فضای جدول روی توسعه خودکار تنظیم نشده باشد، در پایان عملیات جایی برای رشد بخش‌ها وجود نخواهد داشت. برای ایجاد فضا باید اندازه فضای جدول را به صورت دستی تغییر دهید.کوچک کردن ممکن است با شکست مواجه شود، اما اگر هر حرکتی با موفقیت انجام شود، ممکن است اندازه فایل داده را کاهش دهد.ما می توانیم فضای جدول SYSAUX را کوچک کنیم.رویه SHRINK_TABLESPACE اضافه باری دارد که شامل یک پارامتر SHRINK_RESULT out می‌شود، بنابراین نتیجه عملیات را می‌توان به‌عنوان یک CLOB برگرداند، نه اینکه با استفاده از DBMS_OUTPUT خارج شود.منبع مستند